Excellent. This channel probably has the best advanced PowerBI free content on the internet. Moreover, the Italian accent is very funny! Greetings from Brazil.
This series of visually explainations of contexts in DAX, made a great addition to my knowledge. It seems now that its easier to define which function to use in certain cases. Excellent job, Marco! Thank you
I have been stuck the last two days working on this kind of case, you've just solved my next week Thanks a lot, really clear and using simple terms (as the shield) makes it even more comprehensive, love it
¡Excellent explanation. Thank you for sharing with us your work! I am now reading your book “The Definitive Guide to DAX - 2nd Edition”. This is 24 karat gold.
Thank you so much for another great explanatory video Marco. The visual explanation is a huge help understanding why the total average is wrong. I have also struggled with another measure that worked on row level context, but not for the column total, so had to insert an ISINSCOPE for the result variable, as the column total calculation had to be done differently to disregard certain results that was omitted correctly in the row context but not in the total. More videos to help with these situations would be appreciated. Keep up the good work!
Dear Marco, Thank you for another usuful video. Context transition is indeed one of the most difficlt concepts of DAX, and I had strangled with it for months before I finally got it. One remark for the last Keepfilters example though. The only issue of the DAX measure, which didn't use Keepfilters is the Total row. Therefore, I expected the video to focus on that row and explain what is difference in that row compared with the other rows, which where correctly calculated even without using Keepfilters. BTW, using graphical methods to explain DAX concepts will be, in my humble opinion, a great addition to the next revision of "Definitive Guide to DAX" book and perhaps other books SQLBI had previously published.
When explaining how the wrong avg number 1002311.71 is calculated, marco show a table at time 22:11. It will be nice to show the measure here of this table.
Hello Marco Please please do a video of visual representation of CALCULATE Rule. I know most of the rules have been covered here but please do a separate video. Please
Thank you for the explanation !! Your videos are great I can finally understand row context and filter context. I am confused however about keepfilters not working with calculate. In your explanation of REMOVEFILTER case, the context transition is triggered first by CALCULATE, but why in the case of KEEPFILTER its triggered by SUMX? I can get my head around it, shouldn't the order of the code execution from the inner function to outer hence CALCULATE first then SUMX like in the case of REMOVEFILTER?
Thanks for the video. I don´t get why you suggest that for ignoring context transition we have to use KEEPFILTERS in the first argument rather than using KEEPFILTERS in a Calculate. So, I wonder why the suggested AVERARGEX( KEEPFILTERS(DISTINCT(Date[Month])), [Sales Amount]) would provide a different behaviour to AVERAGEX( DISTINCT(Date[Month]), CALCULATE([Sales Amount],KEEPFILTERS(DISTINCT(Date[Month])). I think in both cases KEEPFILTERS applies an intersection between the row context and filter context, so both scenarios would return the same result?. Thanks.
No, the latter would not apply KEEPFILTERS to the context transition. Moreover, you didn't write a predicate to the latter syntax, so the result would just keep the filter obtained by the context transition removing the filter coming from the outer filter context - the effect of the filter argument in CALCULATE would be irrelevant because KEEPFILTERS ( DISTINCT ( Month[Month] ) ) is translated into KEEPFILTERS ( DISTINCT ( FILTER ( ALL ( Month[Month] ), Month[Month] = Month[Month] ) ) ), which would return all the months without removing the existing filter.
Sorry. The formula I provided was not right. I meant this: AVERAGEX ( DISTINCT( 'Date'[Month] ) , CALCULATE([Sales Amount],KEEPFILTERS( 'Date'[Month] IN {"January","February","November","December"})) ). I tried this in your pbix file and provides a wrong result. But why? I thought this would keep these 4 months. For instance, when iterating November in the row context, this will intersect with the Filter context (which has 4 months), and thus returning only November. So, at the end, we would only have the two years and the 4 months we want through context transition. Also, I tried this other option, which adds the Year. AVERAGEX ( DISTINCT( 'Date'[Month] ) , CALCULATE([Sales Amount],KEEPFILTERS( ('Date'[Year],'Date'[Month]) IN {(2019,"January"),(2019,"February"),(2018,"November"),(2018,"December")}) ) ). In this second option, yes it worked. So, in this case, context transition happens and it works. So, what I mean is that we could either use KEEPFILTERS (less code) in the iterator or either use context transition with this second option (don't know why the first option didn't work). However, in the video it only says that we should use KEEPFILTERS in the iterator, it does not mention we could use KEEPFILTERS the way I explaied before. Hope this explains better. Again, thanks for your time for the video and repplying comments.
If you are finding it challenging to focus and keep track while hearing these terms over and over again, this might mean that you need to work on the basics more, make sure you understand them pretty well so that you can directly imagine what the word means, and then return to advanced concepts like the Context transition. I personally watched the basics videos from SQLBI over 10 times until the idea sticked to my head..
If this video explained each word, it would be four hours. I approach DAX like learning a foreign language. For me to learn properly, I study the grammar as it relates to DAX.
Excellent. This channel probably has the best advanced PowerBI free content on the internet. Moreover, the Italian accent is very funny!
Greetings from Brazil.
as difficult as it is, your explanation is greatly appreciated. Thanks, Marco, for your dedication to our community.
This series of visually explainations of contexts in DAX, made a great addition to my knowledge. It seems now that its easier to define which function to use in certain cases. Excellent job, Marco! Thank you
I have been stuck the last two days working on this kind of case, you've just solved my next week
Thanks a lot, really clear and using simple terms (as the shield) makes it even more comprehensive, love it
¡Excellent explanation. Thank you for sharing with us your work! I am now reading your book “The Definitive Guide to DAX - 2nd Edition”. This is 24 karat gold.
Thank you so much for another great explanatory video Marco. The visual explanation is a huge help understanding why the total average is wrong. I have also struggled with another measure that worked on row level context, but not for the column total, so had to insert an ISINSCOPE for the result variable, as the column total calculation had to be done differently to disregard certain results that was omitted correctly in the row context but not in the total. More videos to help with these situations would be appreciated. Keep up the good work!
As always - excellent, insightful, and easy to understand.
Fantastic video, the KEEPFILTERS() before context transition is passed is a concept I just didn't even know about.
These visual explanations are so helpful. Thank you very much.
Super clear and detailed explanation as always, many thanks!
And again, great explanation. Thank you very much. Enjoying DAX 😃
Always enlightening to watch your detailed explanation Marco.
One more excellent video.
Thank you Sir
Dear Marco, Thank you for another usuful video. Context transition is indeed one of the most difficlt concepts of DAX, and I had strangled with it for months before I finally got it. One remark for the last Keepfilters example though. The only issue of the DAX measure, which didn't use Keepfilters is the Total row. Therefore, I expected the video to focus on that row and explain what is difference in that row compared with the other rows, which where correctly calculated even without using Keepfilters.
BTW, using graphical methods to explain DAX concepts will be, in my humble opinion, a great addition to the next revision of "Definitive Guide to DAX" book and perhaps other books SQLBI had previously published.
Coming (not soon) by end of 2025, indeed!
Really nice explanation using whiteboard. Love it.
Appreciate for your efforts for making people educate.
Your earlier videos on DAX were very intimidating...this visual series however is the best on DAX
When explaining how the wrong avg number 1002311.71 is calculated, marco show a table at time 22:11. It will be nice to show the measure here of this table.
Essential, Marco! Thank You!!!!!
I had no idea that you could define variables inside a function (SUMX) 🤯
Please upload more Advanced DAX topics videos
The Sales Top Customer Optimized measure is awesome
Excellent job! It would be great if you made the same visual presentation for different measure calculations)
Hello Marco
Please please do a video of visual representation of CALCULATE Rule. I know most of the rules have been covered here but please do a separate video. Please
Nice explanation
Great expleaning, thanks
Thank you for the explanation !! Your videos are great I can finally understand row context and filter context. I am confused however about keepfilters not working with calculate. In your explanation of REMOVEFILTER case, the context transition is triggered first by CALCULATE, but why in the case of KEEPFILTER its triggered by SUMX? I can get my head around it, shouldn't the order of the code execution from the inner function to outer hence CALCULATE first then SUMX like in the case of REMOVEFILTER?
Check the order of the evaluation in dax.guide/calculate/
Please make some videos regarding how to make complex charts using DAX
Great, Realy it is so educational.
Thanks. Greg will be rolling his eyes again when he sees calculate().. again, even tho you have emphasized so many times why you did it 😅
Thanks for the video. I don´t get why you suggest that for ignoring context transition we have to use KEEPFILTERS in the first argument rather than using KEEPFILTERS in a Calculate. So, I wonder why the suggested AVERARGEX( KEEPFILTERS(DISTINCT(Date[Month])), [Sales Amount]) would provide a different behaviour to AVERAGEX( DISTINCT(Date[Month]), CALCULATE([Sales Amount],KEEPFILTERS(DISTINCT(Date[Month])). I think in both cases KEEPFILTERS applies an intersection between the row context and filter context, so both scenarios would return the same result?. Thanks.
No, the latter would not apply KEEPFILTERS to the context transition. Moreover, you didn't write a predicate to the latter syntax, so the result would just keep the filter obtained by the context transition removing the filter coming from the outer filter context - the effect of the filter argument in CALCULATE would be irrelevant because KEEPFILTERS ( DISTINCT ( Month[Month] ) ) is translated into KEEPFILTERS ( DISTINCT ( FILTER ( ALL ( Month[Month] ), Month[Month] = Month[Month] ) ) ), which would return all the months without removing the existing filter.
Sorry. The formula I provided was not right. I meant this:
AVERAGEX (
DISTINCT( 'Date'[Month] )
,
CALCULATE([Sales Amount],KEEPFILTERS( 'Date'[Month] IN {"January","February","November","December"}))
).
I tried this in your pbix file and provides a wrong result. But why? I thought this would keep these 4 months. For instance, when iterating November in the row context, this will intersect with the Filter context (which has 4 months), and thus returning only November. So, at the end, we would only have the two years and the 4 months we want through context transition.
Also, I tried this other option, which adds the Year.
AVERAGEX (
DISTINCT( 'Date'[Month] )
,
CALCULATE([Sales Amount],KEEPFILTERS( ('Date'[Year],'Date'[Month]) IN {(2019,"January"),(2019,"February"),(2018,"November"),(2018,"December")})
)
).
In this second option, yes it worked. So, in this case, context transition happens and it works. So, what I mean is that we could either use KEEPFILTERS (less code) in the iterator or either use context transition with this second option (don't know why the first option didn't work). However, in the video it only says that we should use KEEPFILTERS in the iterator, it does not mention we could use KEEPFILTERS the way I explaied before.
Hope this explains better. Again, thanks for your time for the video and repplying comments.
Thank you
Is this a re-upload
No, this is a new video!
the first who comment a "thank you" for you content
I would love to learn DAX it's just that I hate it so much... Eh, one mm at a time...
You need to put a little bit of love!
@@SQLBI Will do! :) ❤
I hate for 5 yrs
the biggest mistake this channel is doing is stick to ambiguos terms like filters, transition, and contexts , and insist in repeating them
Please, help us to find alternatives!
If you are finding it challenging to focus and keep track while hearing these terms over and over again, this might mean that you need to work on the basics more, make sure you understand them pretty well so that you can directly imagine what the word means, and then return to advanced concepts like the Context transition.
I personally watched the basics videos from SQLBI over 10 times until the idea sticked to my head..
@@TRZMac Yes , we need to learn again and again.
If this video explained each word, it would be four hours. I approach DAX like learning a foreign language. For me to learn properly, I study the grammar as it relates to DAX.