Share:

Share on facebook
Share on twitter
Share on linkedin

Materialize Calculated Power BI Result Sets

Materialize and Synchronize Power BI with a Data Warehouse

“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.

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 realize this.

A key requirement here is to understand the nature of DAX calculations: these are -contrary to common belief- not associated with 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 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​

Recent Posts

Top 5 Tips & Tricks For Planning, Budgeting and Forecasting in Power BI & Excel In this blog I am

Planning and Analytics [Infographic]

It is not uncommon for companies to have separate planning and analytics processes or legacy solutions that result in more

Materialize and Synchronize Power BI with a Data Warehouse “Materialize” is a database concept that refers to storing the result

I can’t help smiling when I see legacy BI/Planning application vendors -that were not so long ago laughing away Power BI- coming out with their own “Power BI Integration” after Microsoft has turned around the industry. These are more often than not nothing but “a marketing spiel” to build on Power BI’s awareness and of very limited use in real life application scenarios.

Across many mid-sized and large companies, finance and operations teams are at crossroads. This was one of the findings from the recent BPM Pulse 2021 survey which revealed that only a third of companies had truly harmonized strategic, financial, and operational plans.

Tags