How To Apply Context Concept in DAX — DAX in Power BI — Chapter 3

The concept of context enables dynamic analysis while creating DAX formulas.


Experts are saying that if you learn about the context in DAX, then you are going to be a master in DAX. I am following their path and start to understand deeply the concept of context in DAX.

If you have missed my previous two blogs about DAX, please find the link below.

https://arpitatechcorner.com/2021/04/20/introduction-to-dax-in-power-bi/

https://arpitatechcorner.com/2021/04/22/use-of-dax-variable-in-power-bi/

What is Context in DAX

Philip Seamark says 

“Context is how DAX applies layers of filtering to tables used in your calculations so that they return results that are relevant for every value.”

As per Microsoft documentation, context is an important concept for building high-performing, dynamic analyses and for troubleshooting problems in formulas.

That means context is the layer of filtering and is applied to calculations to produce a result related to every value of a visual or pivot table including rows and columns totals.

Types of Context

Some say there are two types of context. But as per Microsoft documentation, there are three types of context.

Image by Author

Get Data

At first import, the data from Kaggle and save it to Power BI Desktop.

https://www.kaggle.com/juhi1994/superstore)

Image by Author

Filter Context

Filter context means when you are applying filters on the set of values of columns or tables using DAX calculations

There are some items on which filter context applied. For example,

  1. attributes in rows or columns 
  2. by slicer
  3. through filter pane
  4. to the calculated measure

“Filter context applies on top of other contexts, such as row context or query context.”

Before any core calculation, the first filter context is finalized. Once it is decided, filter rules (inside context) are applied across the data and calculations execute on the remaining data. After completion of the calculation, filter context is destroyed and not used by any other process.

Implicit Filter Context

Let’s see some example from our code.

Calculated Measure Implicit Filter Context Power BI
Image by author

From the above example, let us find out how you can define the implicit filter context

  1. Here the values of every cell are different because every execution of calculated measure in this pivot view uses a unique filter context.
  2. DAX expression is a simple SUM function with no explicitly mentioned filter conditions like year or region. Then why every value is different! This is the effect of the DAX implicit filter context.
  3. DAX performs separate logical calculation for each cell including each of the total. 
  4. Filter context for calculation is empty but it has two implicit column filters one for a year and another for the region.
  5. All filters are based on a logical AND and only sales values which can meet both the criteria are passed to the SUM function.

Explicit Filter Context

Explicit filter context means in calculations where specifically adds or removes column filter rules to and from the filter context. 

“Explicit filter context is applied after implicit and row context and allows you to customize and sometimes completely override the default behavior of your measures in a way the row and query context can’t.” 

It’s time to do some experiment. 

DAX Filter Context Power BI
Image by Author
  1. From the above report, you can observe CALCULATE function derives the summed value of sales for the explicit filter condition (Sub-Category=”Phones”). 
  2. It ignores the implicit value of Sub-Category within the visual.
  3. That means the explicit filter within the DAX function will override the implicit filter which is in the visual.
  4. If you change the filter condition and use the ALL function, then you can ignore the previous filter context and produce the sales for a total of ALL Sub-Categories.
DAX Filter Context Power BI
Image by Author

5. If you use the FILTER function within CALCULATE function, then observe the visual difference.

DAX Filter Context Power BI
Image by Author
DAX Filter Context Power BI
Image by Author

Row Context

Row Context is related to current rows. If you create a calculation using the calculated column, the row context involves the values of all columns from the current row. If that table has a relationship with the other table, then it includes all the related values from the other table for that row.

There are some iterative functions in DAX over a table. Those functions involve multiple rows during calculation and each with its own row context.

Please find the below example about Row Context.

  1. Create one calculated column Cost which is based on Sales and Profit columns.
  2. Here every row for the Cost column involves the values of other columns from the current row.
DAX ROW Context Power BI
Image by Author

Query Context

The combination of row and filters create the final query for DAX. You can define this is as query context. Users explicitly mention row and filter context for DAX, and DAX implicitly creates the query context from that filter and row context.

Download

Please find the code in the below location

DAX — Chapter 3 https://github.com/arpitag1/Power-BI 

Video

Conclusion

In this blog, the following things have been identified

  1. What is DAX Context
  2. Types of Context
  3. Filter Context ( Implicit and Explicit)
  4. Row Context
  5. Query Context

In my next blog, we will learn more about DAX.

If you have any questions related to this project, please feel free to post your comments.

Please like, comment and subscribe to my YouTube channel which you have already seen. 🙂 Keep Learning.

1 comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: