Working below a DAX formula’s granularity
Vložit
- čas přidán 2. 08. 2024
- How to control the result of a DAX measure used in a report with granularity below the granularity supported by the formula. Article and download: sql.bi/661463/?aff=yt
How to learn DAX: www.sqlbi.com/guides/dax/?aff=yt
The definitive guide to DAX: www.sqlbi.com/books/the-defin... - Věda a technologie
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!
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 CZcams channel!!
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.
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.
Excellent lesson. Thank you, professor Alberto.
With every video or article I see from SQLBI I feel illiterate on DAX.
Excellent class!
Alberto, you are a maestro!
Another amazing DAX video
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
This is just perfect, thank you for sharing
such an important video! grazie!
Superb....this is the root cause
excellent explanation....
Thank you!
In the article too, the link to the CZcams 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!
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.
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
super
very help full video
i would like to know can we include Product / Customer slicing along with Calendar
Yes, of course!
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.
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/
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 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.
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.