Power BI Report Level Measure behind the scenes
Originally posted on Linkedin on Febbruary 2020.
As I’ve already wrote in my previous post, the new Power BI projects frontier and best-practice is the use of standardized and centralized datasets.
Connection to Power BI service to consume datasets uses the Live Connection storage mode (the same for On-Prem SSAS and Azure Analysis Services).
In Live Connection data preparation and data modeling features aren’t available since the model has already been completed, in other words model changes are not allowed.
When connected to Power BI datasets it’s possible to create locally Report Level Measures, in other words “the definition is stored locally in the pbix” and not in the model (the same is valid for On-Prem SSAS Tabular and Azure Analysis Services).
But how does it work behind the scene?
Let’s start unzipping a pbix file obtaining multiple json files defining the report structure.
The Report Level Measures definition can be found in the <unzippedFolder>\Report\Layout file in the “config” attribute:
The attribute value is an escaped json that contains a lot of information (in a real-world report could be very big) and among the others there’s Report Level Measures definition.
The json contains a “modelExtensions” attribute that contains:
- Model name
- Measure name
- Report Level Measure expression
When you use a Report Level Measure in report, the DAX query passed to the live connected model will contain a DEFINE MEASURE directive in order to create a query scope measure to consume on the fly.
To verify it you can simply “catch” DAX query sent from Power BI Desktop to Power BI dataset when using a Report Level Measure putting it in a card and performing a refresh after Performance Analyzer switch on.
The following is an example:
