When to use calculated tables in #dax

In the September release of Power BI Desktop, Microsoft introduced a new important feature: calculated tables. Chris Webb wrote a quick introduction to this feature, and Jason Thomas published a longer post about when to use calculated tables.

The reason of this excitement about this feature is that it adds an important tool to the data modeling capabilities of DAX based tools (even if, at the moment, only Power BI Desktop shows this feature, but I guess that at least Analysis Services 2016 will provide the same capability). Using calculated columns you can materialize the result of a table DAX expression in the data model, adding also relationships to it. Without this tool, you should read the data from outside Analysis Services and then push the data back – and this wouldn’t be possible in Power BI. I implemented similar techniques in the past by using SQL Server linked servers, materializing the result of a DAX query in a SQL Server table, and then importing that table again in the data model. Thanks to calculated columns, today I wouldn’t to this roundtrip and I would save processing time and reduce data model complexity.

Alberto Ferrari wrote an article describing a good use case for calculated tables. The article presents an implementation of a transition matrix between customer categories evaluated automatically based on other measures (for example, the revenues). I suggest you reading Transition Matrix Using Calculated Tables and then try to implement the same intermediate table for the calculation with other techniques (ETL, SQL, …). You will discover that calculated tables help you writing cleaner and faster code for a transition matrix pattern.