SQLBI is a great course but even they haven't been able to dumb down why SUM ignores the row context. This video is the only resource that breaks it down in an easy to understand manner.
None of the various videos explaining context transition, even from SQLBI, had so much assertiveness in demystifying the context transition in DAX. This is the million dollar video. Thank you.
Thanks Ruth! This is a great example to make sense context transition! I am learning this concept from SQLBI as well, actually a measure already had a hidden calculate()there. If you can have another video in the future to talk about context transition pros & cons, and it should be wonderful. I learned context transition cannot work well when we have duplicates, but I still want to understand deeply on that.
What if you have two similar rows? For example in your example if you have 4 rows instead of 3 4th row being exactly the same as the 3rd row? Would "Sum as CC" give you 500 twice (for row 3 and 4) ?
Another thing would have been interesting to show here, writing this when adding a new column to the table: Sum as CC2 = [Sum as measure]. The result might not be what you expect!
If a measure you're using in a visual, it's insanely powerful. Think of it like a supercharged SUMIFS, though it can be used for count, min, max, average, rank and more. I don't understand why you would use a calculate here.
@@CurbalEN The way you explain most of things...it makes so easy to understand. So may be you can make vdo's for BI guys using Azure world for there reporting purposes....if possible then you can make vdo's on following flow ( Multiple source data upload in azure > consolidation in azure > transformation in azure > uploading in azure Analysis service > power bi > uploading in cloud > sharing )
Hi Ruth, Amazing video, I just wanted to make a small clarification. This article on sqlbi actually presents the opposite argument, from what I have gathered. According CALCULATE introduces filter context to calculations, not row context, www.sqlbi.com/articles/understanding-context-transition/ I am just a little confused. Could you tell please clear this up for me?
I think this is confusing. As you said, CALCULATE will return the value per row not per column. But it doesn't look at any value in any other column to use it as a filter. So what you've said in the end @5:48 is not really accurate. For example, if we add another row that has "Konbu" sale of 700, it will not affect "Sum as CC" in the existing "Konbu" row.
At 5:53, Calculate Sum where Product = "Queso Cabrales". Why not Calculate Sum where Discount=10?? I mean on basis of which column it is calculating the value?
coincido con muchos comentarios; explica de forma facil y se entiende a la primera incluso de forma mas clara que otros canales con mas "renombre". Enhorabuena.
is this not that a measure takes on the filter context in a visual while a column measure does not ??? checxk with otehr functions on a column and then put inside calculate ?
Your videos are always great! Very informative and easy to understand. However, your transparency of where you stumbled when originally learning what you're now teaching is my favorite part and why I like your content more than anyone else's! It reminds me that tripping up is part of the learning process. Keep up the wonderful work!
Another great thing to go over would be why certain calculations completely remove the context in a matrix. Example add + 0 to your measure for the purpose of dividing and getting 0 instead of blank. (There may be a better way, perhaps ISBlank to differentiate)
question, would the sum be caused by the product name or the discount amount? wouldn't it be the discount amount? also great Job on this, I'm taking SQLBI but really struggling (I made it as far as context transition and was on the verge of giving up) I'm glad I found your videos this makes more sense
I love your video, it's very cool and simple to understand. However, I think you are missing something (or I missed it from your explanation). Calculate does not take any particular columns as context filters, but takes all previous columns. This is very weird, and I learned it the painful way, when trying to add two calculated columns with very similar sintax and getting a circular reference error... maybe for another dax friday...
I like this topic! Can you explain why measures DO see the rows as filters? :) I've read that we use measures when we want to aggregate values from many rows in a table, but I'm confused by that. I view "aggregate" as being synonymous with "Sum." So based on that description, I'd expect to see the result that the calculated column in your example produced.
Measures are always surrounded by an implicit Calculate. It wasn’t discussed in this video but really needs to be talked about to get the entire picture.
Hi Ruth, Thanks for the video. Just a quick know how question. If measures and calculate columns with Calculate both do a context transition. Why do we prefer measures over Calculated Columns ?? Is it memory space ?
Thanks. Very good explanation. You might want to add a duplicate row to the table and show what happens then as I believe that also will produce unexpected results, correct?
I am calculating Total sales in two ways but it is providing wrong results First way : Total sales = var sale = SUMX( order_details, order_details[unitPrice] * order_details[quantity]) var discount = SUMX( order_details, order_details[discount] * order_details[unitPrice] * order_details[quantity] ) return sale-discount __________________________________________________________________________________________________ and the second way ( i am using sale variable): Total sales = var sale = SUMX( order_details, order_details[unitPrice] * order_details[quantity]) var discount = SUMX( order_details, order_details[discount] * sale ) return sale-discount this two must provide same result ?
Oh my god, I've been using PBI for years already and yet I didn't know about this! And I sometimes still get mixed up with SUM and SUMX because many times they give the same result. Very appreciate these info as it's good to go back to basics :)
Tengo que comentar esto: Después de tantas horas de aprendizaje de Dax, después de cientos de [products names] pasando ante mis ojos, ver el queso de cabrales en una matriz de Power BI me ha hecho feliz.🥰
Your my Hero Ruth!
I went from SQLBI Mastering DAX course to your video to understand context transition. A milion dollar video indeed.
Gracias!
SQLBI is a great course but even they haven't been able to dumb down why SUM ignores the row context. This video is the only resource that breaks it down in an easy to understand manner.
Your teaching method is the easiest to understand by far. Thank you🎉
Thanks Ruth, have read so many articles about context transition, but nowhere it has been explained so well 😊
Thanks 😊
Thank you DAX just made more sense than it did 5 minutes ago before wathcing your video
😊
None of the various videos explaining context transition, even from SQLBI, had so much assertiveness in demystifying the context transition in DAX.
This is the million dollar video.
Thank you.
Thanks!
/Ruth
Agreed
Thanks Ruth! This is a great example to make sense context transition! I am learning this concept from SQLBI as well, actually a measure already had a hidden calculate()there. If you can have another video in the future to talk about context transition pros & cons, and it should be wonderful. I learned context transition cannot work well when we have duplicates, but I still want to understand deeply on that.
Will do!
/Ruth
Thank you soooo much! You gave an easy explanation of such a difficult-to-understand concept.
Nice explanation of CALCULATE() and context filter, thanks
You welcome 🙏!
/Ruth
Bravissima Ruth! 👏👏👏👏👏
🎉🎉🎉
Thank you very much. I watched several videos and I did not get it until now.
Yey!! :)
Why do switch statements or if give correct results in calculated columns then ?
What if you have two similar rows? For example in your example if you have 4 rows instead of 3 4th row being exactly the same as the 3rd row? Would "Sum as CC" give you 500 twice (for row 3 and 4) ?
fun and objective. thank you
Sempre top e direto!
Thanks
Another thing would have been interesting to show here, writing this when adding a new column to the table: Sum as CC2 = [Sum as measure]. The result might not be what you expect!
Will talk about it in another video!
/Ruth
Noob question: so is the calculate function used in a measure just overkill?
If a measure you're using in a visual, it's insanely powerful. Think of it like a supercharged SUMIFS, though it can be used for count, min, max, average, rank and more.
I don't understand why you would use a calculate here.
Vou add vc no netflix
Practice, practice, practice hehehe
Yep, that is the only way!
oh my god, context transitions are my personal demons in learning dax.... must face it now! thank you Ruth!
Will do more on the topic soon :)
/Ruth
But how does it know that Product is the filter for Calculate? Isn't Discount just as usable as a filter for Calculate?
Great questions, one step at a time :)
/Ruth
I had the same question!
Hi,
Your video's are very helpful...
Can you make vdo's on Azure with power bi..✌️
Anything in particular you would like to see?
/Ruth
@@CurbalEN The way you explain most of things...it makes so easy to understand.
So may be you can make vdo's for BI guys using Azure world for there reporting purposes....if possible then you can make vdo's on following flow ( Multiple source data upload in azure > consolidation in azure > transformation in azure > uploading in azure Analysis service > power bi > uploading in cloud > sharing )
The easiest and the best explanation of context transition I have seen so far!!! Brilliant work, Ruth, please keep it up, you are awesome!
Hi Ruth,
Amazing video, I just wanted to make a small clarification.
This article on sqlbi actually presents the opposite argument, from what I have gathered. According CALCULATE introduces filter context to calculations, not row context,
www.sqlbi.com/articles/understanding-context-transition/
I am just a little confused. Could you tell please clear this up for me?
The best explanation for context transition. You nailed it
Thanks!
/Ruth
I love you Ruth how you explain such a complex concept in something so easy in 5 minutes. Excellent video
I think this is confusing. As you said, CALCULATE will return the value per row not per column. But it doesn't look at any value in any other column to use it as a filter. So what you've said in the end @5:48 is not really accurate.
For example, if we add another row that has "Konbu" sale of 700, it will not affect "Sum as CC" in the existing "Konbu" row.
At 5:53, Calculate Sum where Product = "Queso Cabrales".
Why not Calculate Sum where Discount=10??
I mean on basis of which column it is calculating the value?
This is Awesome DAX video, simple and informative. Well done Ruth!
coincido con muchos comentarios; explica de forma facil y se entiende a la primera incluso de forma mas clara que otros canales con mas "renombre". Enhorabuena.
is this not that a measure takes on the filter context in a visual while a column measure does not ??? checxk with otehr functions on a column and then put inside calculate ?
The best explanation I heard so far , Thank you (PBI Trainer) 👏
Great explanation! DAX does my head in...
Your videos are always great! Very informative and easy to understand. However, your transparency of where you stumbled when originally learning what you're now teaching is my favorite part and why I like your content more than anyone else's! It reminds me that tripping up is part of the learning process. Keep up the wonderful work!
It sounds like we have similar learning styles!! ;)
/Ruth
Calculate gave Sum a helping hand. We should learn from this. Let's help our fellow human beings.
😀
Great example!
Thanks again!
Thanks!!
Awesome
Great!!
The best explanation about context transition ever!
Thanks!! 🥳🥳
Do you know you are the best teacher on PowerBI on UA-cam?
No you don't know.
And you are awesome ☺️
Another great thing to go over would be why certain calculations completely remove the context in a matrix. Example add + 0 to your measure for the purpose of dividing and getting 0 instead of blank. (There may be a better way, perhaps ISBlank to differentiate)
Thank you for explaining.
question, would the sum be caused by the product name or the discount amount? wouldn't it be the discount amount?
also great Job on this, I'm taking SQLBI but really struggling (I made it as far as context transition and was on the verge of giving up) I'm glad I found your videos this makes more sense
This is an excellent explanation. It is tricky explaining how the CALCULATE function creates a filter context.
Da Best CT tutorial ! Thank you 😊
🎉🎉
You gave an easy to understand explanation to a difficult concept. Thanks a lot.
Nice job! I am searching for ways to explain row to filter context transition and this is the simplest, most elegant explanation thus far!
Wouldn't this be the time to introduce row context v filter context, i.e., that context transition means the transition from row to filter context?
I don’t understand what you say at all
That is a common DAX problem :(
Wow.. didn't knew this👍👍
Awesome explanation!
Thanks for the tip !
I love your video, it's very cool and simple to understand. However, I think you are missing something (or I missed it from your explanation). Calculate does not take any particular columns as context filters, but takes all previous columns. This is very weird, and I learned it the painful way, when trying to add two calculated columns with very similar sintax and getting a circular reference error... maybe for another dax friday...
Baby steps ;)
/Ruth
I like this topic! Can you explain why measures DO see the rows as filters? :) I've read that we use measures when we want to aggregate values from many rows in a table, but I'm confused by that. I view "aggregate" as being synonymous with "Sum." So based on that description, I'd expect to see the result that the calculated column in your example produced.
Measures are always surrounded by an implicit Calculate. It wasn’t discussed in this video but really needs to be talked about to get the entire picture.
Hi Ruth,
Thanks for the video.
Just a quick know how question.
If measures and calculate columns with Calculate both do a context transition. Why do we prefer measures over Calculated Columns ?? Is it memory space ?
Check this video out:
m.ua-cam.com/video/SmXLgEHXSGc/v-deo.html&vl=en
/Ruth
Thanks. Very good explanation. You might want to add a duplicate row to the table and show what happens then as I believe that also will produce unexpected results, correct?
Will do on next video ;)
/Ruth
How can 'Context Transition' feature be used effectively somewhere else (in measures) and not in Calculated Columns?
I dont know, so many variables that matter...
I am calculating Total sales in two ways but it is providing wrong results
First way :
Total sales =
var sale = SUMX(
order_details,
order_details[unitPrice] * order_details[quantity])
var discount = SUMX(
order_details,
order_details[discount] * order_details[unitPrice] * order_details[quantity] )
return sale-discount
__________________________________________________________________________________________________
and the second way ( i am using sale variable):
Total sales =
var sale = SUMX(
order_details,
order_details[unitPrice] * order_details[quantity])
var discount = SUMX(
order_details,
order_details[discount] * sale )
return sale-discount
this two must provide same result ?
Congratulations (recommanded by Guy in a Cube!) et Merci Beaucoup for that clear explanation !
🥳🥳🥳
Could you please do a video explaining the Table Expansion technique? Thank you in advance!!
I dont know what that is?
/Ruth
So simple, yet so complex.
And that is why we love it, isnt it?
/Ruth
Nice and clean way to explain context transiction. Thanks Ruth
Thanks 2 u and happy friday!
/Ruth
Why did it choose products field? If they were not unique records would it still be the same?
Will talk about it on another video :)
/Ruth
Thanks, Ruth that is extremely useful
Any books that you recommend that will provide a good foundation? Thanks
Oh my god, I've been using PBI for years already and yet I didn't know about this! And I sometimes still get mixed up with SUM and SUMX because many times they give the same result. Very appreciate these info as it's good to go back to basics :)
Will continue with this series :)
/Ruth
Cool! And probably would be good to know if CALCULATE(SUM) is actually the same as SUMX?
Tengo que comentar esto:
Después de tantas horas de aprendizaje de Dax, después de cientos de [products names] pasando ante mis ojos, ver el queso de cabrales en una matriz de Power BI me ha hecho feliz.🥰
A mi me paso lo mismo 😍😍
Thx. I was going crazy trying to understand what is going on.
Yeah, we all have been there, it gets better I promise!!