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.
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]
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!!
thank you for great videos!! I had been wondering why my CALCULATE filters were not working and it's all down to context transition! You explain the fundamentals very clearly!! It took me few times watching to get much better understanding than before and still some trial and errors to finally get my calculation right, thank you so much!!!!
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
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.
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 ).
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.
Thnks ones again for the great material you provide! Now, I'm wondering. Is it essential to uses 'VALUES' as a filter argument in de first example of sales big customers, or can one use 'ALL' or 'DISTINCT' as well. When experimenting in with this example I get 3 times the same result. But maybe is some cases this will produce wrong results?
Hi, At 4:36 time of the video the measure you have created (Sales of Big Customer), should not you use values(sales(CustomerKey)), because outer visual filters that are coming from rows in the matrix directly apply the filter to the sales table they do not directly effect the customer table we always have the same number of distinct customer return by Values function when we applied this values(customer(CustomerKey)), we should use values(sales(CustomerKey)) instead of values(customer(CustomerKey)) because we can gain performance benefits as well because of this approach? Looking forward to your response Thanks!
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)
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.
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?
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 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().
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??
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 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]
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 ?
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
quick question to check whether I understood the concept of context transition correctly. If I were to use "var", context transition would also not be happening, right? RT Sales 1= var maxdate= MAX('Date'[Date]) var calc= CALCULATE( [Sales Amount], FILTER( ALL('Date'), 'Date'[Date]
Context transition would happen only if you write CALCULATE, just MAX would not have triggered the context transition anyway. However, you are right, if the variable gets a value from a measure or use CALCULATE, then the value is stored there and there is no further evaluation when the variable is referenced.
@@SQLBI thank you! I am doing your Master DAX course right now, by the way, and your videos here give me a good opportunity to check what I have learnt in your course so far!
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?
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?
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?
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 : ruclips.net/video/0y1rCOL8DTs/видео.html it should be computed in the current filter context + the row context transformed to a filter context , can someone explain the differnce please ? thanks,
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.
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.
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 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.
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?
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.
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]
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!!
Thanks for trying to shed some light on this part.. this part of the video really had me confused.. 😕.
I watched the video like 5 times. Now I get it so well. You are a life saver!! I love the way you talk
Seen it 2 times, will have to go through one more time to really solidify context transition. Thanks DAX Maestro.
Today is the day I found out that you could write a variable within a SUMX function. 🙏 thanks for this awesome content!
I didn't understand the formula. How variable
You are the best mentor that I saw not only in youtube but online as a whole
Been a Power BI trainer for long time, however, learn a new thing every time I see one of your clips. Thank you.
thank you for great videos!! I had been wondering why my CALCULATE filters were not working and it's all down to context transition! You explain the fundamentals very clearly!! It took me few times watching to get much better understanding than before and still some trial and errors to finally get my calculation right, thank you so much!!!!
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
I just keep on repeating all the different videos and its slowly making more and more sense
It is worth to watch every day .... As you find something daily when you are putting your 100% to it .... Thanks Alberto ❤
I appreciate seeing the explicit writing of equivalent Filter Context in the calculated column.
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
Fantastic explanation and examples. Wish I had known about this a few years ago instead of learning the hard way by trial and error.
Pure Gold specially for Intermediate users..
Loved every second of the tutorial, learnt a lot. Thanks for your efforts in presenting this.
This channel really puts me into a better position when writing DAX, appreciate the great content you guys provided!
I've been watching your videos today and they are an excellent building block.
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.
I was having this problem today. Thanks for explaining it so elegantly.
Watching your videos and learning with you is always so inspiring.
Thank you so much Alberto.
The masters of DAX language!!!!
You are great person, make chance for other to learn those precious info for free.
Thanks Alberto, can not say more thanks to you ;)
these series really amazing... thanks again
Great explanation for each and every video, .
Thank you so much for all your awesome videos.
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 ).
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.
9:40 thanks! I really needed to see it like this!
the contrasting examples are very helpful! great explanation
Well explained as always in a way one is able to grasp the concept.
Very good. I enjoyed watching this video
Thnks ones again for the great material you provide! Now, I'm wondering. Is it essential to uses 'VALUES' as a filter argument in de first example of sales big customers, or can one use 'ALL' or 'DISTINCT' as well. When experimenting in with this example I get 3 times the same result. But maybe is some cases this will produce wrong results?
Hi,
At 4:36 time of the video the measure you have created (Sales of Big Customer), should not you use values(sales(CustomerKey)), because outer visual filters that are coming from rows in the matrix directly apply the filter to the sales table they do not directly effect the customer table we always have the same number of distinct customer return by Values function when we applied this values(customer(CustomerKey)),
we should use values(sales(CustomerKey)) instead of values(customer(CustomerKey))
because we can gain performance benefits as well because of this approach?
Looking forward to your response
Thanks!
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)
Nice way of explainng it! Gracias!
Really nice explanation
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.
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?
RELATEDTABLE is like CALCULATETABLE, see dax.guide/relatedtable
Thank you so much. How did you increase the font size in the formula bar? Is there a keyboard shortcut?
CTRL+Mouse Wheel!
@@SQLBI is there a similar way in Excel too? The settings change isn't as clean.
Probably no.... you have to go in Settings, indeed.
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?
No. It's calculating the MAX for the current filter context, i.e. year and month of the specific "cell" of the matrix visual.
@@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().
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??
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!
No, ALL is like REMOVEFILTERS and remove a filter, it does not create any filter.
@@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]
@@bgoka no there is an implicit calculate function wrapped around [MaxDate], in both scenarios ALL('Date') is same.
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 ?
Thank you @alberto. ❤
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
quick question to check whether I understood the concept of context transition correctly. If I were to use "var", context transition would also not be happening, right?
RT Sales 1=
var maxdate=
MAX('Date'[Date])
var calc=
CALCULATE(
[Sales Amount],
FILTER(
ALL('Date'),
'Date'[Date]
Context transition would happen only if you write CALCULATE, just MAX would not have triggered the context transition anyway. However, you are right, if the variable gets a value from a measure or use CALCULATE, then the value is stored there and there is no further evaluation when the variable is referenced.
@@SQLBI thank you! I am doing your Master DAX course right now, by the way, and your videos here give me a good opportunity to check what I have learnt in your course so far!
Can’t lie, this video confused my destiny.
why can't just use date['date']
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?
Context transition applies to the expanded table!
I realize from this video that I'm still not ready to undertsand filter context transition
Obrigado professor muito bom a explicação
Professor faz um vídeo carrinho de compras por cliente
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?
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?
@@inaction2024 yes that make sense
thank you so much sir.
Context transition is a complex topic if you explain it hard.
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 : ruclips.net/video/0y1rCOL8DTs/видео.html
it should be computed in the current filter context + the row context transformed to a filter context ,
can someone explain the differnce please ?
thanks,
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.
somthing is not clear about a filter date < = max(date) it seems like a totology in fact every date is < or = max date
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.
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] )
)
RELATEDTABLE is just an alias of CALCULATETABLE, so you get basically the same context transition behavior.
@@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.
I wish understand this before hahah
❤❣
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?