As mentioned in earlier posts, Power BI uses an expression language called DAX (Data Analysis Expressions) to create calculations. At first glance, this language is similar to Excel formulas, but there are some important differences.
Most importantly, DAX uses a row and filter context when executing expressions. Having a good understanding of how these context aspects work is key for creating correct and performant measures.
Filter context
If you look at a typical Power BI report, you’ll see slicers, filters and visuals that modify the filter context.
- A slicer is a visual selection element that lets the user filter e.g. a year, a customer, or a product.
- The Visual, Page, and Report filters are defined when the report is created in order to define a subset of data. Most of these filters are slightly hidden; however, after the release of the new filter experience in February 2019, it became easier to understand how your data are filtered.
- There’s also visual filtering, which includes the interaction between two or more visuals as well as the filtering within the visual.
Figure 1 shows a calculation (measure) which is filtered by the values of the Category field. Finally, there are also DAX functions that change the filter context.
Briefly put: defining the filter context means evaluating the combination of all these possible selection and filter options.
Figure 1
Row Context
DAX expressions are calculated in a row context. In my earlier blog post, I mentioned iterator functions, which return the result of an expression evaluated for every row in a table. This is what we call the row context.
The magic of DAX is the fact that you can combine filter and row context: first, you evaluate the filter context, and then you execute the calculation in a row context.
Easy, isn’t it? No? Well, we can help you understand the row and filter context and explain the behaviour of your formulas in one of our Power BI courses. You are quite capable in Power BI , but you still have some doubts? Our Power BI Consultants will also gladly help you optimise your reporting
