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.
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 RUclips channel!!
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!
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
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 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?
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
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
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'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 ?
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.
![Gunna - HIM ALL ALONG [Official Visualizer]](http://i.ytimg.com/vi/HiCxoDeo0hc/mqdefault.jpg)
This channel is one of the best DAX reference out there, and it's free!
Thank you, Alberto
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.
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 RUclips channel!!
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!
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
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.
This is just perfect, thank you for sharing
With every video or article I see from SQLBI I feel illiterate on DAX.
Excellent class!
Excellent lesson. Thank you, professor Alberto.
Alberto, you are a maestro!
Another amazing DAX video
Superb....this is the root cause
excellent explanation....
such an important video! grazie!
In the article too, the link to the RUclips 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!
In third measure instead of using variables, we can use simple condition: if isfiltered( 'Date'[Date]) then blank, else calculate(.....). Thank you!
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.
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
very help full video
i would like to know can we include Product / Customer slicing along with Calendar
Yes, of course!
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/
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'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.
Thank you!
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 👍
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.
super