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

By:   |   Updated: 2017-06-14   |   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

Last Updated: 2017-06-14

get scripts

next tip button

About the author

Comments For This Article


Recommended Reading

How to create calculated tables in a tabular SQL Server Analysis Services SSAS model

Scenarios for Using Calculated Tables in SSAS Tabular 2016 or Power BI Desktop – Part 2

Using Calculated Tables in Analysis Services Tabular 2016 for Aggregate Tables – Part 3

Build a Cube From an Existing Data Source Using SQL Server Analysis Services

Comparing Data Warehouse Design Methodologies for Microsoft SQL Server

get free sql tips
agree to terms