Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


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

By:   |   Last Updated: 2017-06-14   |   Comments   |   Related Tips: 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

Last Updated: 2017-06-14

get scripts

next tip button

About the author

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Learn more about SQL Server tools