The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.

**Save €200 with code MSCUST on top of early bird pricing!**

- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

- Power BI forums
- Forums
- Get Help with Power BI
- DAX Commands and Tips
- Re: Losing context transition by using filter

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Losing context transition by using filter

05-04-2024
08:28 PM

Hi All,

Struggling to understand this behaviour of calculate. Newbie here. I have two tables. A sales table and a product table. I am creating a calculated column inside product table to to calculate sales of each product only when the quantity sold (in sales table) is greater than 1.

In the screenshot, you can see that Calculate doesn't do context transition when using a filter inside. However, if I write the formula as

single calculate=calculate(sum(Sales[pk_total sales amount]),Sales[Quantity]>1) , it enforces this context transition.

I am also beginning to wonder if this above new formula is deceiving my logic. Isn't the above new formula for single calculate the same as

single calculate = calculate(sum(pk_total sales amount), filter(all(sales[quantity), sales[quantity)>1). If yes, then context transition must not have been obeyed in the first place!

It also obeys context transition if I use calculate twice . example

Double calculate = calculate(calculate(sum(Sales[pk_total sales amount]), filter(sales,Sales[Quantity]>1)))

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-06-2024
06:28 AM

@pankajk83

As I mentioned in my previous reply, there is absolutely no difference, in this case, between using FILTER - ALL or FILTER - VALUES. In fact VALUES, DISTINCT, ALLSECTED will all perform as ALL because the is no outer filter context.

What you are missing here is that the filter argument of CALCULATE replaces the filter context created by CALCULATE. When placed in a calculated column, CALCULATE converts all the columns of the current row of the table into a newly created filter context. When you use FILTER - Table you actually replace that filter context with a new one. Except this time the new filter context includes all the rows, not only the current row. That is the very same effect of FILTER - ALL ( Table ).

When you use FILTER - ALL ( Table[Column] ) you only replace that column in the filter context created by CALCULATE but the rest of that filter remains.

My recommendation, avoid using CALCULATE in creating calculated columns but rather try to rely on a NoCALCULATE approach.

8 REPLIES 8

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-05-2024
05:15 AM

Hi @pankajk83

The reason is very simple. CACULATE imposes context transition over the first Argument (the expression). However, the 2nd argument (filter argument) of CALCULATE is evaluated in the outer filter context. But guess what! There is no outer filter context in this case. It's a calculated column with only row context.

When you wrap the whole formula with another CALCULATE then the inner calculate in its totality becomes the expression argument of the outer CALCULATE. Therefore, context transition is applied and hence "FILTER ( 'Table'..." becomes "FILTER ( CALCULATETABLE ( 'Table' )..."

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-06-2024
05:30 AM

Thank you. However, why does the same formula obey context transition when I use the ALL function

sing calc = calculate(sum(Sales[pk_total sales amount]), filter(all(Sales[Quantity]), Sales[Quantity]>1))

I have taken a look at the other answers and these are great insights. I am still trying to get some clarity.

I completely agree with what you have written here in your answer, but should it not be the same when you use Filter(ALL(sales[quantity),.....) just as it is for Filter(sales,........)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-06-2024
05:39 AM

@pankajk83

Yes that is becuase ALL(Sales[Quantity]) removes the filter from only that column. I would also say that FILTER ( VALUES ( Sales[Quantity] ),... would also give the same result.

Accordingly, without the outer filter context FILTER ( Sales,... is equivalent to FILTER ( ALL ( Sales ),...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-06-2024
05:45 AM

Apologies that I might be dragging this a little too far, but you see, using ALL, as you said, removes the filter from only that column. However, I am not doing this in a visual but in a calculated column. In thet case, there is no filter context at all when filter is first evaluated. So I am confused why does the presence of ALL make any difference.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-06-2024
06:28 AM

As I mentioned in my previous reply, there is absolutely no difference, in this case, between using FILTER - ALL or FILTER - VALUES. In fact VALUES, DISTINCT, ALLSECTED will all perform as ALL because the is no outer filter context.

What you are missing here is that the filter argument of CALCULATE replaces the filter context created by CALCULATE. When placed in a calculated column, CALCULATE converts all the columns of the current row of the table into a newly created filter context. When you use FILTER - Table you actually replace that filter context with a new one. Except this time the new filter context includes all the rows, not only the current row. That is the very same effect of FILTER - ALL ( Table ).

When you use FILTER - ALL ( Table[Column] ) you only replace that column in the filter context created by CALCULATE but the rest of that filter remains.

My recommendation, avoid using CALCULATE in creating calculated columns but rather try to rely on a NoCALCULATE approach.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-05-2024
03:49 AM

@pankajk83It all has to do with CALCULATE's evaluation order and Expanded Tables.

CALCULATE (

SUM ( Sales[pk_total sales amount] ),

Sales[Quantity] > 1

)

SUM ( Sales[pk_total sales amount] ),

Sales[Quantity] > 1

)

The first thing that happens here is evaluation of Sales[Quantity] > 1 which becomes FILTER ( ALL ( Sales[Quantity] ), Sales[Quantity] > 1 ) which is evaluated in the external filter context that is whatever is outside CALCULATE. This argument could have been something like FILTER ( VALUES ( Sales[Quantity] ), Sales[Quantity] > 1 ) therefore it is first evaluated in the external filter context, and since in a Calculated Column there is no external filter context when this argument is evaluated it isn't affected by anything from the other columns.

After this Context Transition happens and all the values of all the columns(even the calculated ones) of each row are transformed into equivalent filter context, this will be applied in intersection with the Sales[Quantity] > 1

If there are any CALCULATE modifier such as USERELATIONSHIP, CROSSFILTER, ALL, REMOVEFILTERS, ALLEXCEPT, ALLSELECTED they are applied next so that they can modify the effect of Context Transition and NOT THE FILTER PREPARED IN THE FIRST STEP i.e. Sales[Quantity] > 1

After this whatever remains in Filter Context from context transition and filter arguments is applied to the first argument of CALCULATE and then the measure/table is evaluated. Please note if required the First step will overwrite the Context Transition, Context Transition can't overwrite the Filter arguments of CALCULATE unless code is altered specifically.

This is why your calculation works fine.

CALCULATE (

SUM ( Sales[pk_total sales amount] ),

FILTER ( Sales, Sales[Quantity] > 1 )

)

FILTER ( Sales, Sales[Quantity] > 1 )

)

Here table expansion comes into picture, the reference to Sales is not just Sales table alone but all other tables that can be reached from Sales following a Many : 1 relationship, example: products, customers, dates, a FILTER on Sales here will filter all the other Dimension tables as well.

Context Transition by default in a single CALCULATE affects only the first argument of CALCULATE, here it doesn't affect FILTER ( Sales... part of the code.

In this example, first whole model(sales, dimension1, dimension2, dimensionN) is filtered for Sales[Quantity] > 1 and kept aside, then context transition happens, but after context transition the result of FILTER ( Sales... is applied which overwrites the effect of context transition.

Since you're applying only one filter to Sales and then applying that to the first argument of CALCULATE you get grand total sales that only considers Quantity > 1

CALCULATE (

CALCULATE (

SUM ( Sales[pk_total sales amount] ),

FILTER ( Sales, Sales[Quantity] > 1 )

)

)

Since you're using 2 CALCULATE and the inner CALCULATE is the first argument of outer CALCULATE when the outer CALCULATE initiates context transition it will filter the FILTER ( Sales... part that's why you will see the correct result because Sales is filtered for appropriate rows by context transition + Sales[Quantity] > 1

In this case the steps are:

Outer CALCULATE initiates Context Transition and invalidates any row context, no more row context are available for context transition after this.

Inner CALCULATE will first evaluate FILTER ( Sales ... in the external filter context, what is it? the row context transformed to equivalent filter context by outer CALCULATE.

Inner CALCULATE can't perform context transition because there aren't any row context available as outer CALCULATE invalidated them.

CALCULATE will apply FILTER ( Sales.. to the first argument and evaluate the measure.

This is why you shouldn't reference the full table in DAX, not only are they hard to understand but they also impact the performance adversely and can result in calculations that are incorrect.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-04-2024
11:56 PM

@pankajk83

Before reading my answer, I would suggest you to go through these two blogs

https://www.sqlbi.com/articles/expanded-tables-in-dax/

https://xxlbi.com/blog/power-bi-antipatterns-9/

https://xxlbi.com/blog/power-bi-antipatterns-10/

I would like to answer your question in two parts, first lets see the difference between, filter function in calculate and predicate filter in calculate.

-- filter predicate in calculate

single calculate=calculate(sum(Sales[pk_total sales amount]),Sales[Quantity]>1)

when you are filtering on a column within calculate as filter predicate that means you are filtering on the expanded table (which includes columns from sales and product).

The equivalent of above DAX expression is:

single calculate=calculate(sum(Sales[pk_total sales amount]), Filter(all(Sales),Sales[Quantity]>1) )

However, with the below syntax you are filtering on sales table only.

single calculate=calculate(sum(Sales[pk_total sales amount]), Filter(Sales,Sales[Quantity]>1) )

Now coming to the next part, first of all

One of the rule for calculate function is, calculate fitler arguments are evaluated in original row and filter context (i.e row context in your scenario) and the result of these arguments then effect the filters created due to context transition.

with filter predicate (Sales[Quantity] > 1) or (Filter(all(sales), sales[quantity] >1):

- The resulting filter context includes columns from both the Sales and Product tables, and filters produced due to context transition from the Product table will affect the result.
- The CALCULATE function evaluates its expression within this modified filter context, taking into account any filters applied by the FILTER and ALL functions.

with double calculate with filter function:

- Here, a FILTER function is nested inside a CALCULATE function to filter the Sales table based on the quantity column.
- The inner CALCULATE evaluates its expression within the filter context created by the FILTER function, which filters the Sales table.
- The outer CALCULATE evaluates the result of the inner CALCULATE within its own filter context, potentially affecting the result through context transition.

with Filter(Sales, Sales[Quantity] > 1)

- Here also the filter argument is evaluated in original filter context but the result will not contain the columns from product table (not an expanded version of sales)
- So, though the filters due context transition are not observable)

Note: I am not an expert in the core concepts of DAX. I wrote the above answer based on my understanding, so take it with a grain of salt.

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-04-2024
09:17 PM

correct me if I'm wrong but guessing:

Are you using CALCULATE() to make a calculated column? If that is the case that might be causing you trouble. Let me explain more about the logic:

single calculate makes the SUM() over the table, because there is no context transition at all. the measure would be working fine in a matrix if it's calculated in a measure, not in a column.

I don't know why the other two are working fine, but my theory is that ALL() is forcing to make the calculate in every different row.

It would be very useful to us you share the pbix file or your data to run some tests.

Hope been useful.

PD: sorry about my English still learning.

Top Solution Authors

User | Count |
---|---|

39 | |

30 | |

25 | |

21 | |

20 |

Top Kudoed Authors

User | Count |
---|---|

72 | |

33 | |

32 | |

31 | |

19 |