Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          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:   |   Read Comments   |   Related Tips: 1 | 2 | 3 | 4 | > Analysis Services Development


ALERT: Share your SQL Server knowledge and become a MSSQLTips author


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

decoder table DAX (datatable)

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

decoder table DAX (union)
Next Steps


Last Update:


signup button

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    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools