Among the Power BI beginners are several users with many years of experience working with Excel. However, in this scenario in particular, I regularly see that procedures from Excel are transferred directly to Power BI and users wonder why the hoped-for results are not achieved. A common reason for this is that these users do not use measures as well as are not familiar with the concept of filter context.
If you want to build effective solutions in Power BI, you can't get around Measures. Therefore, in this article I will provide you with an understanding of the basic properties of measures and build a bridge to another fundamental topic in working with Power BI, namely the filter context.
If you have already worked a lot with pivot tables, you will even notice many parallels between Power BI Measures and the calculation behavior of a pivot table. The only difference is that with Power BI Measures you have many more options to influence the calculations via the DAX language.
Introduction to Power BI Measures
Simply described, measures in Power BI are the equivalent of formulas in Excel. Measures can be defined in Power BI Desktop in both the Data Model View and the Report View. Measures are based on the formula language DAX, which stands for Data Analysis Expression.
When do measures come into play?
Once you have loaded data into Power BI Desktop using Power Query and your data model is ready, you typically start performing calculations directly using Measures. Measures are your most important tool when it comes to calculating KPIs and metrics.
Implicit vs. explicit measures
In Power BI, a basic distinction is made between implicit measures and explicit measures.
Implicit Measures: After loading the data into your data model, Power BI scans your tables and guesses which columns could potentially be used for calculations. In principle, this applies to all columns that are not used as keys and contain only numerical values. These columns are automatically marked with a sum symbol. Power BI thus implies that in case these columns are used, a summation aggregation should be performed.
The example below shows the use of such a column within a card visual.
Now we come to the counterpart of the implicit measures, namely the explicit measures.
Explicit Measures: These are measures that you define yourself and explicitly determine what they should do. Maybe you don't want to draw the sum, but the average. Maybe you want to consider additional multiplication or use an additional filter within the formula. As soon as you have created an explicit measure, you will find it marked with a calculator in the table layout on the right side.
Below you can see an explicit Measures defined with the DAX formula SUM(), which is also used within a Card Visual.
Please do not use implicit measures: We want to determine ourselves how Power BI calculates. That's why we don't even start using implicit measures. We always create explicit measures so that we retain control over the calculation logic.
Measures vs. Calculated Columns vs. Calculated Tables
As described above, the DAX language is used for the definition of measures. However, the DAX language can also be used for other objects, namely for so-called Calculated Columns and Calculated Tables. This is not our focus in this article, so here is only a brief description for the classification of these objects.
Calculated Columns are separate columns which can be added to a table of the data model using DAX. In a Calculated Column, the DAX formula used is executed for each row of the table and a value is output immediately according to the formal logic.
Calculated Tables are comparable to Calculated Columns. Only, as the name suggests, an entire table is calculated using the DAX formula language. These tables can then be used directly in the data model, just like tables from Power Query, and can be linked to other tables via relationships.
Note on Calculated Columns: Even though Calculated Columns are only touched upon here I would like to add that I use them extremely rarely and always avoid them if possible. The use of Calculated Columns is even widely considered bad practice. One reason for this is that column calculations can also be done in Power Query and data provided from Power Query is stored more efficiently in Power BI Desktop. When I personally build dashboard solutions, I always first look to see if the requirement can be solved via explicit Measures or via Power Query. Only in exceptional cases do I resort to Calculated Columns.
How Power BI Measures works
After a short introduction regarding Measures, DAX and the other objects, we now come to the big comparison between Power BI Measures and classic Excel formulas with the aim to better understand how Measures work.
Since a lot takes place in Excel in tabular form, we use a matrix visual in Power BI for comparison. Therefore, I will also discuss elementary differences between Excel spreadsheet and Power BI matrix in this framework.
In recent years, there have of course also been some innovations in the Excel world as well as new formulas, which bring completely new possibilities with them. However, since the focus here is on Power BI Measures, I will only use classic Excel functions for the comparison.
Presentation of the sample data
To help you understand the following points, I'll use a simple scenario with sample data. In my example, we have a single table of sales transactions in an Excel file. The table contains the following columns
- Date
- Customer Key
- Customer Name
- Product Key
- Product Name
- Channel Key
- Channel Name
- Value (sales value)
While I leave the data as is in Excel, I prepare the data in Power BI Desktop using Power Query according to the star schema. You can read more about the star schema in this post find. After performing the ETL activities, my Power BI data model are as follows:
For each dimension (Date, Product, Customer and Channel) there is a separate dimension table. The primary keys are marked with PK_ and the foreign keys are marked with FK_. There is a 1:n relationship between the fact table and the dimension tables.
Presentation of the fundamental features of Power BI Measures
Now we come to the explanation of the fundamental properties of Power BI Measures. Especially for the first points, I will first describe the classic Excel behavior and show how a Power BI Measure behaves differently.
Power BI Measures calculate with table, column and measure references
Excel formula:
In the Excel formula definition, the calculation works via references to cell ranges or individual cells. This also makes it very easy to understand which values are included in the calculation. I can also look at the individual values that are referenced directly in the spreadsheet.
Power BI Measure:
Measures do not refer to cells or cell ranges, but to entire tables or table columns of the data model as well as to other measures. If only certain rows of a column are to be taken into account, this is done using filters. We will learn more about how these filters work in the later chapters when it comes to the filter context. Unfortunately, the classic reference to a cell or cell range does not exist in Power BI. This is typically also one of the biggest challenges when switching from Excel to Power BI.
Power BI Measure storage location and usage location are detached from each other
Excel formula:
In the Excel formula, the usage location also corresponds to the storage location. If you create a formula in an Excel cell E2, the corresponding result will also be displayed in this cell. If you want to use the formula in another cell (e.g. E3), you can copy the formula from E2 to E3 or rewrite the formula in E3. However, both formula definitions are independently customizable in themselves. If I adjust the formula definition in E3 later, the formula definition in E2 remains unchanged. So, technically, both formulas are independent objects unless they refer to each other. Nevertheless, it is true for E2 and E3 that the storage location is also the calculation location.
Power BI Measure:
A measure is created separately and is not bound to a table or column. When the measure is created, it is assigned to a home table, but this has no influence on the calculation or on the subsequent use of the measure. It is completely irrelevantat which table the measure is located. the measure can target all tables and columns to include them in the calculations. Therefore, it is also common practice to create a separate table that does not contain any data and is used exclusively for storing the measures, a so-called measure table. A single measure can be used in multiple visuals. However, if I change the formula of the measure later, these changes will affect all visuals that use the measure directly or indirectly.
Power BI Measure calculations occur only when used in visuals
Excel formula:
Immediately, when the formula is created within a cell, a result is directly returned in the cell.
Power BI Measure:
The calculation of the formula stored in the measure does not take place immediately after creation, but only when the measure is actually used within a visual. As soon as the measure is inserted into the visual via drag & drop, the measure starts to read the data from the data model taking into account the formula definition, then it performs the calculation and returns the result.
Power BI Measures Replicate Within Visuals
Excel formula:
An Excel formula is always calculated only in the cell in which the Excel formula was actually inserted. A written formula, throws a result in exactly one cell. So if I want to use the same formula for several cells, I have to copy it manually.
Power BI Measure:
On the other hand, several calculations can be performed on the basis of one single measure.
For example, if you use a matrix visual, you can fill it with dimension fields in the row separator and the column separator of the visual. Looking at our example, you could create a row drilldown by customer and a column drilldown by product. As soon as you add the measure to the value field of the matrix visual, the following happens:
- Power BI calculates the measure separately for all combinations that result from the dimension characteristics (customer and product)
- For each individual calculation, the corresponding filter context (see next chapter) is evaluated and a result is returned
- If a combination actually returns a value (i.e. is not BLANK() or empty), then the dimension expression is also listed and the value is shown
- In addition to the combinations themselves, subtotals and the grand total are also returned.
In our example, based on a single measure inserted into a matrix visual with 5 customers and 5 products, there are a total of 36 calculations. This results from the 25 dimension combinations, 10 sub totals and the overall total.
Power BI Measure results are determined by the (interactive) filter context
- Excel formula:
The result of an Excel formula is basically determined by two criteria:
- the formula definition based on the Excel functions
- the cells and cell ranges that are referenced
Power BI Measure:
The result of a measure is fundamentally determined by three criteria
- the formula definition based on the DAX language
- the tables and columns, to which reference is made
- the so-called evaluation context which consists of the row context and the filter context
At this point I deliberately exclude the tow context and concentrate on the filter context, since in my experience this is more relevant for the start. Nevertheless, I give you at least a short explanation for the row context at this point: Row context in Power BI is what happens in Excel when you work with the SUMPRODUCT() formula. But now let's get to the filter context.
Filter Context: The filter context is the combination of all filters that act during the calculation of a measure. These filters are created when fields (typically from dimension tables) are used in the following objects:
- page filter
- visual filter
- slicer created on the page
- dimension values of the rows and columns
- dimension characteristics of the axes or legends in charts
The special thing about this is that these fields do not have to be explicitly referenced in the formula and they still act as filters. Whether and how the fields generate filter context is determined by the data model and in particular by the relationships between the tables.
The following image extends the representation used above by the filter context.
The following two images show how filter context acts on a visual from the outside, but also how filter context is created within the visual.
Power BI Measures can redefine filter context
As described above, the filter context can be changed e.g. by adjusting the slicer selection which even allows the report consumer to change the filter context. However, there is a list of functions that allow evaluation and adjustment of the filter context. These are for example the functions CALCULATE(), FILTER() or ALL(). But there are many more. At this point it is important to understand that the measure can handle and change filter context coming through visuals, slicers, etc..
For example, in the example below, the filter context resulting from the customer name is overwritten by the measure. Although each customer has its own row, the Measures displays the values of customer 1 in each row by using the CALCULATE() function.
For the explanation of the different functions and what happens technically in the background when using them, a multitude of articles can be filled. Since this article is intended as an introduction to the world of Measures, you should first understand that there are filter contexts, that they are created in various ways, and that they can be manipulated using various functions.
The fact that the filter context can be created by various dimensions and even influenced by the report consumer, e.g. by slicers, quickly creates complexity. Especially if there is no clean data model, the requirements become more complex and there is additionally a lack of sound knowledge about the functions in this context, errors quickly occur in the report. Therefore, it is even more important to set up clean data models from the beginning, which follow established best practices.
Conclusion
This post gave you an introduction to the essential features of Power BI Measures. In addition, we took a first look at the fundamental filter context concept. I hope you were able to glean the following points from the article.
- Measures are your number 1 tool for the implementation for calculations in Power BI
- As a rule of thumb, you should prefer measures over calculated columns
- Write always explicit measures using the DAX language
- Measures refer in the calculations to tables, table columns and other measures
- A direct reference to cells or cell areas (as in Excel) is not possible
- For the calculation results it is completely irrelevant, in which home table the measure is located
- Not the measure creation, but the usage in a visual triggers calculations
- One measure can be used in several visuals become
- One Measure replicates itself when using it in visuals with dimension breakdowns
- Besides the formula itself, also the Filter context determines the ultimate calculation result
- Filter context is generated by various objects, e.g. slicers, page filters or drilldowns within visuals
- The Filter context can be changed using a variety of DAX functions become
- Effective measures require a clean data model as well as sound understanding of filter context
I'm so glad you made it this far! Stay datenhungrig and see you soon!
Copyright notice and this permission notice for Microsoft icons used - https://github.com/microsoft/PowerBI-Icons: MIT License - Copyright (c) Microsoft Corporation. Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
