dashboard

Share:

Share on facebook
Share on twitter
Share on linkedin

How to Materialize DAX Calculations into a Power BI Model

“Materialize” is a database concept that refers to storing the result of a query, often in conjunction with a SQL view. The advantage here is that when using the result set you can query stored values as opposed to having to run calculations with typically a significant improvement in response time.

Materialize and Synchronize Power BI Model with a Data Warehouse

Recently a customer asked us to “materialize” DAX calculations in a Power BI model to their data warehouse. In this blog, I will cover a very simple approach to realizing this.

A key requirement here is to understand the nature of DAX calculations: these are -contrary to common belief- not associated with a table in Power BI but a “freestanding” semantic concept that can be associated with any table of the model (even a calculated one – more here later).
The results of a DAX calculation only exist in conjunction with a query or table, so we need to look at materializing that.

Typically, you have 2 options in Power BI: either create a calculated table or add a calculated column with the measure(s) that you want to materialize.

1. Calculated Table

This approach will use a DAX measure like SELECTEDCOLUMNS() to create a measure table with columns from the source table that you want to use with the measure and the measure(s):

Calculated Table Power BI

2. Calculated Column

This approach uses a straightforward addition of a calculated column to the table where you want to materialize:

Calculated Column Power BI

Simple Export

A simple approach to materialize either result set is to export the table data file and load that to where you need the materialized results.

Automated loading to a Data Warehouse

A more elegant and automated way is available via the Acterys Power BI Sync External Tool which can be downloaded from this link: https://landing.acterys.com/power-bi-sync and only takes a few seconds to install.

Power BI External Tool Acterys Power BI Sync

Once installed you can just configure the access details of your target server where you want to store the materialized tables and then just point to it and click on export:

Export Power BI Calculated Table to SQL Server

Power BI Sync also allows you to automate this task and run the load process in a chosen time interval:

Automate Export To Data Warehouse
Result Set in SQL Server

Write-back & Planning

With an Acterys subscription this table is then also immediately “write back” enabled and can be used with all the planning features that are included in the solution:

Edit Tables in Power BI​