Scenarios for Using Calculated Tables in Analysis Services Tabular 2016 or Power BI Desktop – Part 1

By:   |   Comments   |   Related: 1 | 2 | 3 | 4 | > Analysis Services Development


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.


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):

decoder table DAX (datatable)

There is also an alternative syntax which you can use:

Decoder2 = 
       { ( "Low",0,50)
        ,( "Medium",50,80)
        ,( "High",80,100)}
       , "Category", [Value1]
       , "LowRange", [Value2]
       , "HighRange", [Value3]
decoder table DAX (selectcolumns)

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 = 
    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.

decoder table DAX (union)
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck 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 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

Comments For This Article

get free sql tips
agree to terms