Brilliant! I've usually struggled with controlling the result shown below the intended granularity. This is a great step-by-step tutorial of how to manage the result above and below!
You're such a master with DAX, I watched the video and I realized that I need to understand better the fundamentals in order to understand the code you share with us. Great TH-cam channel!!
Hi Alberto! The last solution seems very complicated to me, I just would to this: High Months (3) = IF(NOT( ISFILTERED('Date'[Date])), [High Months (2)]) Seems to have the same outcome, are there any issues I oversee?
Really useful to see the different approaches based on the customer need. The explanation of High Months 3 showing how to return the result of each variable step by step was helpful in understanding how you built the measure and why you had to use remove filters instead of AllExcept. Thank you.
Hi, Based on your lesson , I can propose shorter solution for "the blank option whenever the report goes below the supported granularity" High Months (4) = CALCULATE ( SUMX( VALUES ( 'Date'[Calendar Year Month]) , var daysNo= count('Date'[Calendar Year Month]) Return if([Sales Amount]>30000 && daysNo>1 ,1)), all('Date'[Calendar Year Month]), VALUES ( 'Date'[Calendar Year Month] ) )
@@SQLBI Hi again, my option High Months (4) is giving the same result as your High Months (3) expression , please have a look drive.google.com/file/d/1QurWVEGUUtujdpdUS0lbuB70OeTksALW/view
I've seen this video 6 times with today's, evry time it gives me hope to learn DAX. We need a book with different training tasks for evry concept and function FROM ZERO TO MAX. I'll by it imidently. Thank you
This is the ultimate gold of how DAX really works and enabling data models for large number of users to self serve without explaining each of them how that measure really works and why it is displaying this results. Thank you Alberto and a must watch for all Tabular model designers.
To force the measures at correct granularity, I am thinking about the following pattern: 1. Create a set of "Granularity level" measures to identify the granularity e.g. "Calendar Level" which provides if the calculation is happening at Year, Month or Day level. (reference: www.daxpatterns.com/hierarchies/) 2. In the measure development, determine the granularity using above "granularity level" measures and then change the context accordingly and calculate the expression. This might help in standardization of measure development at correct granularity.
I feel in the DAX book, we are providing lots of concepts and details about functions/modeling first and then explain measures. It is really overwhelming for the first time reader.
Without understanding measures first in a simple way, it is very difficult to understand how these various concepts (evaluation context, transition, lineage, relationships, iterators, granularity etc) work in sync and we never understand the dynamics of concepts working together. Due to this, when we reach to write advanced measures, we can't since we did not understand the concepts in the first place. I think the book should start upfront with simple measure without any tables and slowly develop measures in increasing complexity using advanced concepts (evaluation context, relationships etc). This would force students to understand the concepts with respect to the measure and they can understand the dynamics of interrelated concepts much better. I am reading the DAX book for the fourth time, and this time, reading each chapter again relating with measure development and slowly the brain has started inter-relating the concepts wrt measures.
Hi Alberto, When I write the "High Months (Wrong)" measure as follows, then I also get the correct values. I'm curious why this formula works when you put the filter arguments in a outer calculate: High Months (Wrong) = CALCULATE ( SUMX ( VALUES ( 'Date'[Calendar Year Month] ), IF ( [Sales Amount] > 30000, 1 ) ), ALL ( 'Date' ), VALUES ( 'Date'[Calendar Year Month] ) ) Thanks,
is it correct to say that context transition is effectively does not happen when we input something in the filter argument of CALCULATE? Or it just replace part of the filter context generated by context transition? Based on my understanding the context transition only happen on the in CALCULATE , but the filter argument will modify the filter context generated by context transition in the end
I'm currently reading the Definitive Guide to DAX for the first time, I'm pretty much a newbie in the DAX world so I kind of get the concepts to an extent and understand the formulas but can't think of the formulas by myself... is it normal? What are your suggestions? Thanks in advance Alberto!
Just practice! The evaluation context is a unique concept that does not have similarities with other languages. Start with simple reports and task, it usually takes months before you start "thinking" in DAX... don't give up!
Context transition, learnt from Definitive Guide to DAX first edition. Enjoying DAX with you. 👍 Can we use isinscope() to get the right level in hierarchy to get rid of 1 at day level ?
![[Full Episode] The Restaurant War Thailand ศึกพ่อค้าซ่าแม่ค้าแซ่บ Episode 2 | 29 ก.ย. 67](http://i.ytimg.com/vi/r3lfLNH6Ke4/mqdefault.jpg)
With every video or article I see from SQLBI I feel illiterate on DAX.
Excellent class!
This channel is one of the best DAX reference out there, and it's free!
Thank you, Alberto
Brilliant! I've usually struggled with controlling the result shown below the intended granularity. This is a great step-by-step tutorial of how to manage the result above and below!
This is just perfect, thank you for sharing
super
Excellent lesson. Thank you, professor Alberto.
You're such a master with DAX, I watched the video and I realized that I need to understand better the fundamentals in order to understand the code you share with us.
Great TH-cam channel!!
Hi Alberto!
The last solution seems very complicated to me, I just would to this:
High Months (3) = IF(NOT( ISFILTERED('Date'[Date])), [High Months (2)])
Seems to have the same outcome, are there any issues I oversee?
If the user wants to see at the week granularity you'll have problems with your code, as weeks are below the month granularity but above days.
Really useful to see the different approaches based on the customer need. The explanation of High Months 3 showing how to return the result of each variable step by step was helpful in understanding how you built the measure and why you had to use remove filters instead of AllExcept. Thank you.
such an important video! grazie!
Superb....this is the root cause
Hi,
Based on your lesson , I can propose shorter solution for "the blank option whenever the report goes below the supported granularity"
High Months (4) =
CALCULATE (
SUMX( VALUES ( 'Date'[Calendar Year Month]) ,
var daysNo= count('Date'[Calendar Year Month])
Return
if([Sales Amount]>30000 && daysNo>1 ,1)),
all('Date'[Calendar Year Month]), VALUES ( 'Date'[Calendar Year Month] )
)
It's an option, too - sometimes users are surprised by the UI behavior doing that (it seems something doesn't work).
@@SQLBI Hi again, my option High Months (4) is giving the same result as your High Months (3) expression , please have a look
drive.google.com/file/d/1QurWVEGUUtujdpdUS0lbuB70OeTksALW/view
In the article too, the link to the TH-cam video can be provided so that someone reading the article directly would not miss this video.
Thanks for reporting! We just added the video to the article, it seems we forgot to include that link!
I've seen this video 6 times with today's, evry time it gives me hope to learn DAX.
We need a book with different training tasks for evry concept and function FROM ZERO TO MAX.
I'll by it imidently. Thank you
You probably want this book :)
www.sqlbi.com/books/the-definitive-guide-to-dax-2nd-edition/
@@SQLBI Yes Thank you
Another amazing DAX video
This is the ultimate gold of how DAX really works and enabling data models for large number of users to self serve without explaining each of them how that measure really works and why it is displaying this results. Thank you Alberto and a must watch for all Tabular model designers.
Alberto, you are a maestro!
In third measure instead of using variables, we can use simple condition: if isfiltered( 'Date'[Date]) then blank, else calculate(.....). Thank you!
To force the measures at correct granularity, I am thinking about the following pattern:
1. Create a set of "Granularity level" measures to identify the granularity e.g. "Calendar Level" which provides if the calculation is happening at Year, Month or Day level. (reference: www.daxpatterns.com/hierarchies/)
2. In the measure development, determine the granularity using above "granularity level" measures and then change the context accordingly and calculate the expression.
This might help in standardization of measure development at correct granularity.
excellent explanation....
I feel in the DAX book, we are providing lots of concepts and details about functions/modeling first and then explain measures. It is really overwhelming for the first time reader.
Without understanding measures first in a simple way, it is very difficult to understand how these various concepts (evaluation context, transition, lineage, relationships, iterators, granularity etc) work in sync and we never understand the dynamics of concepts working together. Due to this, when we reach to write advanced measures, we can't since we did not understand the concepts in the first place.
I think the book should start upfront with simple measure without any tables and slowly develop measures in increasing complexity using advanced concepts (evaluation context, relationships etc). This would force students to understand the concepts with respect to the measure and they can understand the dynamics of interrelated concepts much better.
I am reading the DAX book for the fourth time, and this time, reading each chapter again relating with measure development and slowly the brain has started inter-relating the concepts wrt measures.
Thank you!
Hi Alberto,
When I write the "High Months (Wrong)" measure as follows, then I also get the correct values. I'm curious why this formula works when you put the filter arguments in a outer calculate:
High Months (Wrong) =
CALCULATE (
SUMX (
VALUES ( 'Date'[Calendar Year Month] ),
IF (
[Sales Amount] > 30000, 1 ) ),
ALL ( 'Date' ),
VALUES ( 'Date'[Calendar Year Month] )
)
Thanks,
That's because there is no context transition thats taken place when you directly write the expression in the row context.
is it correct to say that context transition is effectively does not happen when we input something in the filter argument of CALCULATE? Or it just replace part of the filter context generated by context transition? Based on my understanding the context transition only happen on the in CALCULATE , but the filter argument will modify the filter context generated by context transition in the end
Yes, context transition only affects the expression. Check out evaluation order here: dax.guide/calculate/
very help full video
i would like to know can we include Product / Customer slicing along with Calendar
Yes, of course!
I'm currently reading the Definitive Guide to DAX for the first time, I'm pretty much a newbie in the DAX world so I kind of get the concepts to an extent and understand the formulas but can't think of the formulas by myself... is it normal? What are your suggestions? Thanks in advance Alberto!
Just practice! The evaluation context is a unique concept that does not have similarities with other languages. Start with simple reports and task, it usually takes months before you start "thinking" in DAX... don't give up!
@@SQLBI Thanks fot the advice! it means a lot to me.
Context transition, learnt from Definitive Guide to DAX first edition. Enjoying DAX with you. 👍
Can we use isinscope() to get the right level in hierarchy to get rid of 1 at day level ?
Outside of the SUMX, yes.
@@SQLBI Yes, thank you 👍