Understanding context transition

Context transition is one of the most obscure topics for DAX newbies. Learn how it works, its effects, and how to leverage it rather than be scared of it.
Article and download: sql.bi/751407?aff=yt
How to learn DAX: www.sqlbi.com/guides/dax/?aff=yt
The definitive guide to DAX: www.sqlbi.com/books/the-defin...

[@drod96]

Your passion for people to understand the fundamentals is refreshing. I learn so much from you and I am able to address my very specific problems with much more confidence. Thank you so much, I will be hopefully have the pleasure of you and your team help us out as we are rapidly utilizing Power BI for the company I work for. Thank you so much.

[@ctew]

Today is the day I found out that you could write a variable within a SUMX function. 🙏 thanks for this awesome content!

[@QasimAli-gy3nb]

Seen it 2 times, will have to go through one more time to really solidify context transition. Thanks DAX Maestro.

[@masoudpesaran9721]

Been a Power BI trainer for long time, however, learn a new thing every time I see one of your clips. Thank you.

[@shayshalev3287]

You are the best mentor that I saw not only in youtube but online as a whole

[@salvatorescofano6756]

Oh my god, if I had seen this class before Alberto, I wouldn't have wasted so much time fixing project mistakes! thank you so much

[@willwildwest31]

I watched the video like 5 times. Now I get it so well. You are a life saver!! I love the way you talk

[@shashipaul6279]

It is worth to watch every day .... As you find something daily when you are putting your 100% to it .... Thanks Alberto ❤

[@lorenzotillmaniii4273]

I just keep on repeating all the different videos and its slowly making more and more sense

[@mwaltercpa]

I appreciate seeing the explicit writing of equivalent Filter Context in the calculated column.

[@joaorataoo]

Watching your videos and learning with you is always so inspiring. Thank you so much Alberto.

[@Zunidrap]

Fantastic explanation and examples. Wish I had known about this a few years ago instead of learning the hard way by trial and error.

[@kcmusicable]

I was having this problem today. Thanks for explaining it so elegantly.

[@xiaojienan7891]

Thanks you itis the best explaination. DAX really needs someone attention to details , otherwise itis soo easy to make mistakes . Once master it, it’s powerful

[@HelloKotha]

Loved every second of the tutorial, learnt a lot. Thanks for your efforts in presenting this.

[@beginho2454]

Thanks Alberto, can not say more thanks to you ;) these series really amazing... thanks again

[@mikeycbd]

I've been watching your videos today and they are an excellent building block.

[@BISimplifier]

Pure Gold specially for Intermediate users..

[@davidchang8963]

This channel really puts me into a better position when writing DAX, appreciate the great content you guys provided!

[@bobert13581]

the contrasting examples are very helpful! great explanation

[@joelngige5776]

Well explained as always in a way one is able to grasp the concept.

[@joaquimcosta952]

The masters of DAX language!!!!

[@manarzamrik3978]

You are great person, make chance for other to learn those precious info for free.

[@ArmandoReportes]

9:40 thanks! I really needed to see it like this!

[@nidhisingh4973]

Thank you so much for all your awesome videos.

[@sreenit128]

Great explanation for each and every video, .

[@williamarthur4801]

Really interesting point, re creating max date as a measure, I'd never considered that aspect and not sure I really understand it but thanks to you I'm at least aware of it, will persevere.

[@scramiro1]

Nice way of explainng it! Gracias!

[@matthewdufty606]

Very good. I enjoyed watching this video

[@user-ro3lx3yt8p]

the ony thing that I would add to this video is explaning the moment (14:55), that when calculate is impicitly introduced when we use a measure [MaxDate] in expression : FILTER(ALL('Date'), 'Date'[Date]

[@crownambassador1]

Thank you, at the video 14:55 , I was wondering how is this happening that it returns total for all dates. Your explanation made it easy for me to understand. Thank you once again!!

[@shafa7668]

Really nice explanation

[@praticandopowerbi1220]

Obrigado professor muito bom a explicação

[@praticandopowerbi1220]

Professor faz um vídeo carrinho de compras por cliente

[@user-dh6wx3fe6y]

I realize from this video that I'm still not ready to undertsand filter context transition

[@user-of9vc5vv6m]

Thank you @alberto. ❤

[@peanut-d-cat]

thank you so much sir.

[@sbn4862]

Hi Mr Alberto.Thanck You very much for extra explanations. Is a context transition hapens when there is an active context only? What about , when i use vareble with temporary calculateing table where i have active context row? (and sorry for my bad Englsh)

[@eniolaabolade8638]

Can’t lie, this video confused my destiny.

[@hbird8071]

Thank you for this masterclass. Did not even know this concept existed. I just have a question regarding using the SalesAmount code in the Calculated Column without the implicit 'Calculate' that comes from using the measure. In that particular example if we use the Customer Key relationship and use the RELATEDTABLE function would we be able to achieve the same analytical result, and would it be an example of filter context only equivalent or is there context transition?

[@SQLBI]

RELATEDTABLE is like CALCULATETABLE, see dax.guide/relatedtable

[@nishantchawla333]

Yes, I have a question, that, when we are using context transition, why are we specifically capturing customer values as a variable and making it explicit, it is still the same as implicit right? The value comes out to be same, can you throw more light on it.

[@svengrutzmacher945]

Thank you for this great video! I am just curious: if context transition would happen on the fact table, e.g. SUMX(Sales, CALCULATE(xxx)), does the resulting filter context contain all columns of the sales table or of the expanded sales table? (and thus perhaps all columns of the whole model). After all DAX does always consider the expanded table?

[@SQLBI]

Context transition applies to the expanded table!

[@sandeepbarge4699]

Thanks once again for this series. So MAX used in last expression to calculate running total the right way is giving maximum date up to currently iterated row introduced by FILTER function and not the max date available in the date table. Right?

[@olemew]

No. It's calculating the MAX for the current filter context, i.e. year and month of the specific "cell" of the matrix visual.

[@karannchew2534]

@@olemew Hi, I don't get it, please help. Doesn't ALL(date[date]) clear all the filter in the matrix/visual? I understod that ALL() is the same as REMOVEFILTER().

[@shivamkapoor1827]

At 14.26 why the output is same for all the rows. you said that calculate change row context to filter context but their is also outer filter context from matrix view. what about that filter context why that filter context not shows its effect with the other filters that changes from row context to filter context by canculate??

[@vitaliburla8498]

I think one point could be a little unclear is in very the end - if I'm not mistaken, when the context transition happens, the All statement is also converted to filter context, thereby returning the absolute latest date for the table. So it's like Calculate(max(Date[Date]), ALL([Date]) + all the other columns from the row context. Otherwise, if we just call the measure MaxDate, it will return the current date in the respective row, because of the transition. Or maybe I got it wrong and I'll watch again... Thank you very much!

[@SQLBI]

No, ALL is like REMOVEFILTERS and remove a filter, it does not create any filter.

[@bgoka]

@@SQLBI As I understand correctly basically due to context transition [MaxDate] is under the influence of ALL ( 'Date' ) which removes the filter context (year month) of visual and return maximum date for the whole 'Date' table which then is used for Date[Date]

[@saravananram3740]

@@bgoka no there is an implicit calculate function wrapped around [MaxDate], in both scenarios ALL('Date') is same.

[@khalilayari3230]

what happens if we just put 2 nested sumx without the filter transition ? Will the function just ignore the values[customerKey] and just give the sum with the outter filter context ?

[@Wzxxx]

1. Why SUMX is itarating through VALUES is used instead of ALL? Is there a specific reason for that 2. Why the VAR is defined in the middle of other function (Sumx)? Does the sumx uses it on the go? Will it work without the VAR inside? I didn't find such concept in the book or on your channel ? 3. The more important one for me: Why in the last example the MAX(date[date]) is being evaluated in the original context??? It is a part of FILTER(ALL(dates)) calculate argument which REMOVES filter from a date column so it SHOULD remove outer filter context from date? It iterates through date table without filter and now the MAX(date[date]) still uses original context? Where is logic here?

[@karannchew2534]

Notes for future revision. 1:25 Context Transition (CT) = CALCULATE() transforms any row context to filter context. Context Transition = CALCULARE() mixes Row context in a table + Filter context from a visual/matrix/formula. Context Transition = If row context is present, CALCULATE() takes the row context, and use (aka transform aka transit) them as filter, while doing the row-by-row calculation. Context Transition happens in every CALCULATE(), which also happen in every [measure]. Context Transition requires CALCULATE(). Context Transition also requires row context. Row context exists in: 1. calculated columns 2. certain types of functions that iterate through a table e.g. SUMX 3. table created by FILTER() or other table functions ///////////////////// The first argument of FunctionX() is a table. Here, SUMX( VALUES( Customer[CustKey] )) returns a table of unique customer keys. With this table, the second argument (from VAR to RETURN) run for each row of the table. Sales of big customers = SUMX ( // *Iterate* For each unique customer... VALUES ( Customer[Customer Key] ) // ... get its total sales // Use CALCULATE() to combine "each *row of iterated* unique customer" with whatever filter exist in the visual/matrix... VAR CustomerSales = CALCULATE ( SUMX ( Sales, Sales[Qty] * Sales[Price] ) ) // ... otherwise, "CustomerSales=SUMX(Sales,Sales[Qty]*Sales[Price] )" get the sum based only on whatever filter exist in the visual VAR BigSale = IF ( CustomerSales >= 5000, CustomerSales ) RETURN BigSale ) ///////////// Running Total Sale Wrong = CALCULATE ( [Sales Amount], FILTER( ALL ( 'Date' ), 'Date'[Date] CALCULATE() runs through each row of the table! // --> FILTER( 'Date'[Date] FILTER ( For all/whole Date table, get 'Date'[Date] FILTER ( All dates lower or same as the highest 'Date'[Date] of the visual/matrix context ).

[@nidhisingh4973]

Hi Karan, Thank you for detail explanation. One doubt related to Running Total correct calculation Ideally All(date) removes all filter from date table then how max(date[date]) is computed over visual filter. Can you please explain.

[@gunasekharreddy230]

Hi Alberto , I have created a new column of Big customer sales , The inner query should have filter context for each customer , Indeed the same row filter context is applied on every row of orders table , Can you please explain what if we write the Big customers measure as a column

[@mathijs9365]

Context transition is a complex topic if you explain it hard.

[@ajit78910]

❤❣

[@ellukayou]

I wish understand this before hahah

[@anuphp3432]

why can't just use date['date']

[@inaction2024]

Please tell me I am not the only one who is more confused after watching this video. :-( So, row context existed in column [Date] because of the using of Filter, which is a iterator, and then the row context was transitioned into filter context because of implicit "Calculate" wrapped around the measure MaxDate. Who can tell me how the newly introduced filter context works after that? Also, to me the using of measure MaxDate and the expression of Max (Date [Date]) in the condition doesn't make a real difference in terms of what table it returns. They both return the identical full Date table, doesn't it?

[@inaction2024]

Okay, so here is what I think is actually happening: the context transition created by inexplicit "Calculate" on measure MaxDate created an new internal filter context on the calendar[date] columns, which overrides the existing external filter context applied by visual filter on calendar[date], hence returned the incorrect results. Am I correct?

[@saravananram3740]

@@inaction2024 yes that make sense

[@benromdhanemohsen2765]

somthing is not clear about a filter date < = max(date) it seems like a totology in fact every date is < or = max date

[@SQLBI]

The MAX(date) in a measure is evaluated for every cell of the report, if you have a Matrix with months on the rows, every cell produces a different result.

[@danielabdelsater1541]

Thank you for this amazing explanation. Minute : 15:18 --> you said the MaxDate is not computed in the current filter context, MAXDate is computed in a 'NEW' filter context where the date is only the currently iterated date. But, base on the explanation of your colleague : kzbin.info/www/bejne/ZqqUo3aFgZ13itU it should be computed in the current filter context + the row context transformed to a filter context , can someone explain the differnce please ? thanks,

[@SQLBI]

Why do you say that? MaxDate is a measure reference, so there is an implicit context transition that converts the iterator where MaxDate is into a filter context that hides the outer filter context over dates. That is a "new" filter context, obtained by the existing filter context (the cell evaluated in the report) + the result of the context transition, which *in this case* overwrites the previous one.

[@scramiro1]

Could you even use the following expression to avoid using CALCULATE? Gives same result in my AdventureWorks, but I wonder why better CALCULATE. SUMX ( VALUES( Customer[CustomerKey] ), SUMX ( RELATEDTABLE(Sales) , Sales[Order Quantity] * Sales[Unit Price] ) )

[@SQLBI]

RELATEDTABLE is just an alias of CALCULATETABLE, so you get basically the same context transition behavior.

[@scramiro1]

@@SQLBI So, RELATED does context transition as of CALCULATE? that explains a lot! I thought several times that RELATED do makes a filter transition, but so far I never read the formula worked as a CALCULATE.