The Established Context

A significant part of our interactions with customers venturing into Internal Power BI Development revolves around grasping the concept of Context within the platform. Ross recalls his experience with the SQLBI Mastering Dax course, where, during the final afternoon session, the group struggled to keep up as Alberto introduced increasingly intricate context questions (and that’s without mentioning “Optimizing Dax”). The takeaway is that understanding context in Power BI is a critical but often unclear aspect necessary for moving beyond the beginner level. It’s a continuous cycle of learning, relearning, revisiting, and sometimes feeling overwhelmed by Context in Power BI. There’s always more to master, and this article aims to help beginners progress while offering insights that might assist more advanced users in revisiting or reinterpreting context-related concepts.

There are (now) three Contexts that we need to consider as we write Power BI Dax statements, regardless of whether they are tables, columns, or measures.

Filter Context is the flow of data through the model; your fact table is being filtered by which dimensions? This is a core component of column aggregation because it allows us to remove quantities from the column.

This can give us a value for 2023 because we can limit the “height” of the factSales[Total] column to the values that are within the 2023 filter context from the Calendar because that relates to a list of 365 dates and so sales that were undertaken in 2024 will not be presented to the measure. Filter Context is the most straightforward context to consider as you can understand how your data model works and how you get to the correct answer by following the model you have diligently built. Row Context and – the new – Visual Context are more subtle and can trip up the unaware.

Row Context – The simplest example of Row Context comes when we look at Calculated Columns. Our SUM ( factSales[Total] ) may well come from a calculated column.

(please don’t do this is it a terrible way to work with fact tables, far better to complete this during the ETL phase)

Each of the elements within the calculated column is a column but there is no aggregation, now logic says you cannot multiple a column by a column, you must first aggregate them i.e. total them first then multiply the totals together. Calculated Columns however are evaluated row by row, meaning the column is “filtered” to the current row by default it may be simpler to understand a calculated column as below:

NOTE: CURRENTROW ( ) is a made-up function to aid in the understanding of this example

The row to use is embedded within the context that it is being used. X Functions function similarly; we build or define a table, and then the measure calculates it row by row. This means that

Give the same result as

Remember, filter context will also still be applied, so if you wanted to see Total Sales for 2023 as we did before, both results would respect that filter. The key difference here is that the X version of the measure will be slower to calculate than the non-X version. As a column aggregation data solution, Power BI will SUM ( ) the column much quicker than it can build a table with a calculated column and then SUM ( ) that result.

Finally we have Visual Context, this is something that has only just become a reality and in many ways it is effectively a “Row” type context, the difference and the reason for it being separate is that it only relates to the Visual that it is being used within. To understand this, you must first recognise that every visual in Power BI is behind the scenes table. That table is made up of elements from the semantic model; some will be Text values (rows or columns), and some will be aggregation results. The Visual Context is a Row Context evaluation from that table. This is a direct result of the new Visual Calculations functionality. Look at the table below that we have based on comparing our factSales[Totals] with the Average sales amount (unfortunately, at the time of writing, you cannot calculate this as a visual calculation, although MOVINGAVERAGE ( ) is now much, much simpler.

Visual Context allows the Visual Calculation to be used to add a column for Difference from Average

This measure will not exist within the context of your semantic model; it is a component of the individual visual alone rather than any broader – dare I say it – context.

The appreciation that there are these three contexts available to you is as we have already said the first step in improving your skills overall, as you progress you will learn when context needs to be manipulated and refocused. For example, our SUMX ( ) is very slow because it is looping through the transactions, but it would be possible to use the daily, monthly or even product-focused solution (providing the data rules within our organisation supported that), so lowering the number of rows. The point of this article, then, is to make sure you think about the context that your measures are running against and to not be afraid to revisit that context to improve it.

Geordie Consulting, a dedicated consultancy, has partnered with numerous companies to tackle this challenge. We are committed to sharing our expertise and experience with clients by supporting your Centre of Excellence. Our consultants and their knowledge will be available to foster the growth and development of this function within your organisation. Our team will oversee your Power Platform estate, ensuring consistent refresh cycles. Additionally, we will mentor your team or facilitate knowledge-sharing events to promote a new Data Culture within your business. As a Microsoft Partner, we ensure our team stays current with platform updates. We can simplify the upskilling process for your internal team by guiding and supporting their learning and demonstrating solutions implemented during your engagement. To optimise your return on investment, consider enrolling your organisation in our Centre of Excellence as a Service offering.