By: Koen Verbeeck | Comments | Related: 1 | 2 | 3 | 4 | > Analysis Services Development
Problem
Analysis Services 2016 comes with a lot of DAX improvements. One of these improvements is the ability to create calculated tables. In this tip, we’ll explore one of different use cases for creating such a calculated table in your model.
Solution
With the release of Analysis Services 2016, you can create custom calculated tables using the DAX query language. The tip How to create calculated tables in a tabular SQL Server Analysis Services SSAS model explains how you can create a calculated table in SQL Server Data Tools (Visual Studio) for a SSAS Tabular project. The same concept exists in Power BI Desktop models, which use basically the same engine as SSAS Tabular. At the time of writing, this feature doesn’t seem to exist in Power Pivot.
This article presents the first of a couple of interesting use cases. In this series, we’ll cover the following topics:
- Creating small lookup tables (the subject of this tip)
- Generating a Date table
- Calculating an Aggregate table
Small Fixed Tables
The first use case is when you need a small table with a couple of hardcoded values. You can use this table for lookups or maybe for helping you out with complex calculations. Such a table can be created with row and table constructors in DAX.
For example, suppose we need a small table to decode a percentage value into three categories: small for values between 0 and 50%, medium for values between 50 and 80% and large for values between 80 and 100%. You could code this logic into a calculated column using the IF function, but what if this calculation is needed at multiple places? Will you repeat the logic? What if you need to change one of the ranges? This would mean you need to update your logic in multiple places. With a decoder table, you have a central location to store the logic and you can update it in one place. With DAX, the statement for our calculated table would look like this:
Decoder = DATATABLE (
"Category", STRING,
"LowRange" , INTEGER,
"HighRange", INTEGER,
{
{ "Low",0,50}
,{ "Medium",50,80}
,{ "High",80,100}
}
)
The result is as follows (screenshots are taken in Power BI Desktop, but the result is the same in SSAS Tabular):
There is also an alternative syntax which you can use:
Decoder2 =
SELECTCOLUMNS(
{ ( "Low",0,50)
,( "Medium",50,80)
,( "High",80,100)}
, "Category", [Value1]
, "LowRange", [Value2]
, "HighRange", [Value3]
)
Personally, I prefer the first syntax, as you explicitly assign data types. You can also use a UNION style syntax to generate a table:
Decoder3 =
UNION (
ROW ( "Category", "Low", "LowRange", 0, "HighRange", 50)
, ROW ( "Category", "Medium", "LowRange",50, "HighRange", 80)
, ROW ( "Category", "Hight", "LowRange",80, "HighRange",100)
)
Again you do not specify any data types, but there’s also quite some redundancy in specifying the column names.
Next Steps
- More information on calculated tables:
- You can find more Analysis Services tips in this overview.
- For more SQL Server 2016 tips, you can use this overview.
About the author
Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
