topic Re: How can DAX compare a column to itself in DAX Commands and Tips
https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-can-DAX-compare-a-column-to-itself/m-p/1911782#M41320
<P><LI-USER uid="308234"></LI-USER> </P><P> </P><P>Yes, your understanding is correct. And yes, DAX formulas are counterintuitive to those who start their journey with this language. Because this language is different from anything one has seen before; it's not object-oriented and it's functional. To really understand it one has to be intimately familar with the notion of CONTEXTS and their interaction with other features of the language.</P><P> </P><P> </P><LI-CODE lang="csharp">// A much better formulation of your measure
// is this one:
[Previous Month Sales] =
var MaxDateVisibleInCurrentContext =
MAX( 'Date'[Calendar Month] )
var DateOneMonthAfter =
EDATE( MaxDateVisibleInCurrentContext, 1)
var Result =
CALCULATE(
[Sum of Sales],
'Date'[Calendar Month] = DateOneMonthAfter
)
return
Rasult
// This, in fact, is the standard way
// that it should be written. Don't be fooled
// by the boolean condition in the second
// argument of CALCULATE. All filters in CALCULATE
// are ALWAYS tables. What you see above is just
// syntactic sugar. The code above has at least
// 2 advantages over the other ones:
// 1) It's more readable and understandable at
// a glance and
// 2) it has the potential to be faster since
// such conditions like "T[Col] = Value" under
// CALCULATE have been optimized internally.</LI-CODE><P> </P><P> </P><P>As for the book... There's only one book to rule them all: "The Definitive Guide to DAX" by Alberto Ferrari and Marco Russo. Buy it, read it at least 3-4 times (you'll have to do it anyway, even without my nudging) and practise a lot. I use this site/these forums to actually test my knowledge and I tend to pick up the problems that others leave without answers. This is how I've hardened my DAX (and in fact Power BI) skills. Also, I always keep up to date with articles written and recorded by The Italians (Alberto and Marco). I frequently visit <A href="http://www.sqlbi.com" target="_blank" rel="noopener">www.sqlbi.com </A>since it's the corner of the Internet where all the knowledge of DAX ultimately resides.</P><P> </P><P>By the way, it doesn't matter how you write boolean conditions that contain "=". "a = b" is totally equivalent to "b = a". As far as I remember, this is an axiom of logic.</P>Mon, 21 Jun 2021 09:29:55 GMTdaxer2021-06-21T09:29:55ZHow can DAX compare a column to itself
https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-can-DAX-compare-a-column-to-itself/m-p/1910148#M41292
<P>I came across this formula:</P><P> </P><P>Previous Month Sales =<BR /> CALCULATE(<BR /> [Sum of Sales],<BR /> FILTER(<BR /> ALL('Date'[Calendar Month]),</P><P> MAX('Date'[Calendar Month]) = EDATE('Date'[Calendar Month], 1)<BR /> )<BR /> )</P><P> </P><P>My understanding is that the ALL function removes all the implicit filters on the column 'Date'[Calendar Month]. DAX then iterates over that column to see whether each row satisfies the condition MAX('Date'[Calendar Month]) = EDATE('Date'[Calendar Month], 1).</P><P> </P><P>My question is: how does the boolean expression in the FILTER function work? In particular, what are the values returned by MAX('Date'[Calendar Month]) and EDATE('Date'[Calendar Month], 1)?</P><P> </P><P>Say the first row of 'Date'[Calendar Month] is January 2013, then MAX('Date'[Calendar Month]) would return January 2013 whilst EDATE('Date'[Calendar Month], 1) would return February 2013. Is that right? But if so, no row in the 'Date'[Calendar Month] satisfies the equality comparison, and the Previous Month Sales would be zero?</P><P> </P><P>Thanks in advance.</P>Sun, 20 Jun 2021 01:01:51 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/How-can-DAX-compare-a-column-to-itself/m-p/1910148#M41292nemo2021-06-20T01:01:51ZRe: How can DAX compare a column to itself
https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-can-DAX-compare-a-column-to-itself/m-p/1910276#M41302
<P><LI-USER uid="308234"></LI-USER> <BR /><BR />Let's say you are executing this measure against Month-Year in a Table visual. <SPAN>MAX('Date'[Calendar Month]) refers to the current month in each row but you have used EDATE which expects a date to shift my given number of months and supplied a Month-year. <BR />You can modify your measure as :<BR /></SPAN></P>
<LI-CODE lang="markup">Previous Month Sales =
CALCULATE(
[Sum of Sales],
FILTER(
ALL('Date'),
MAX('Date'[Date]) = EDATE('Date'[Date], 1)
)
)</LI-CODE>
<P><SPAN><BR />I suggest you utilized other standard methods with :<BR /><BR /><A href="https://docs.microsoft.com/en-us/dax/dateadd-function-dax" target="_blank">https://docs.microsoft.com/en-us/dax/dateadd-function-dax</A><BR /><A href="https://docs.microsoft.com/en-us/dax/previousmonth-function-dax" target="_blank">https://docs.microsoft.com/en-us/dax/previousmonth-function-dax</A><BR /><BR /><BR /><BR /></SPAN><BR /><BR /></P>Sun, 20 Jun 2021 10:54:42 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/How-can-DAX-compare-a-column-to-itself/m-p/1910276#M41302Fowmy2021-06-20T10:54:42ZRe: How can DAX compare a column to itself
https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-can-DAX-compare-a-column-to-itself/m-p/1910449#M41306
<P> </P><LI-CODE lang="csharp">[Previous Month Sales] =
CALCULATE(
[Sum of Sales],
FILTER(
ALL( 'Date'[Calendar Month] ),
MAX( 'Date'[Calendar Month] )
= EDATE(
'Date'[Calendar Month],
1
)
)
)
// is equivalent to
[Previous Month Sales] =
var MaxMonthVisibleInCurrentContext = MAX( 'Date'[Calendar Month] )
return
CALCULATE(
[Sum of Sales],
FILTER(
ALL( 'Date'[Calendar Month] ),
var CurrentlyIteratedMonth = 'Date'[Calendar Month]
return
// EDATE works only on real dates, so
// I assume that 'Date'[Calendar Month] is
// for instance the first day of the month
// for each month.
EDATE( CurrentlyIteratedMonth, 1 )
= MaxMonthVisibleInCurrentContext
)
)
// ALL does not remove any filters on any column,
// even more so any implicit filters, but IGNORES
// any EXPLICIT filters on the column and returns
// all the values as they would be seen
// if there were no filtering on the table whatsoever,
// be it implicit or explicit. This is very different
// from removing filters.
// What the measure tries to achieve is dependent on
// the model and especially on the structure of the
// Date table. If there's a Date column in the table
// then it should be used under EDATE, not Calendar Month
// which should be an integer or a name of the month.
// It could, of course, also be the first day of the month
// and it would also work but I doubt this is the setup here.</LI-CODE><P> </P>Sun, 20 Jun 2021 18:28:53 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/How-can-DAX-compare-a-column-to-itself/m-p/1910449#M41306daxer2021-06-20T18:28:53ZRe: How can DAX compare a column to itself
https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-can-DAX-compare-a-column-to-itself/m-p/1910805#M41309
<P>Hi <LI-USER uid="239341"></LI-USER>, thank you for the unexpected answer. Without your detailed explanation, I don't think I could understand what's going on behind the formula of the calculated measure in question.</P><P> </P><P>Also thank you for pointing out my technically incorrect phrase "removes the all the implicit filters". Next time I'll remember to use the term "ignore" instead of "remove"! <span class="lia-unicode-emoji" title=":slightly_smiling_face:">ðŸ™‚</span></P><P> </P><P>To ensure I've understood you correctly, this is my interpreation on how the calcuation takes place.</P><P> </P><P>Assume there is a visual table with a row header 1-Feb-2018. Then</P><P> </P><LI-CODE lang="csharp">var MaxMonthVisibleInCurrentContext = MAX( 'Date'[Calendar Month] ) = "1-Feb-2013"</LI-CODE><P> </P><P> </P><P>That value of 1-Feb-2013 is then used to figure out what is the value for CurrentlyIteratedMonth. That is,</P><P> </P><LI-CODE lang="csharp">EDATE( CurrentlyIteratedMonth, 1 )
= MaxMonthVisibleInCurrentContext
= "1-Feb-2013"
// Therefore:
CurrentlyIteratedMonth = "1-Jan-2013"</LI-CODE><P> </P><P> </P><P>The derived value 1-Jan-2013 is then used for the measure [Sum of Sales], which achieves the desired outome (returns the previous month sales for the current context).</P><P> </P><P>If my interpretation above is correct, then I think the syntax of the formula is counterintuitive. Because <SPAN>MAX('Date'[Calendar Month]) is a known value (implied by the current context) whilst the EDATE('Date'[Calendar Month], 1) is a variable dependent on MAX('Date'[Calendar Month]), then MAX('Date'[Calendar Month]) should have been in the right-hand side of the boolean filter expression, that is, it should have been EDATE('Date'[Calendar Month], 1) = MAX('Date'[Calendar Month]) instead of </SPAN><SPAN>EDATE('Date'[Calendar Month], 1) = MAX('Date'[Calendar Month]).</SPAN></P><P> </P><P>I'll wait for your confirmation of my clarification before accepting your (great) answer as the solution.</P><P> </P><P>Warmest regards,</P><P> </P><P>PS: I can see that you're very smart to make the right assumption that <SPAN>'Date'[Calendar Month] is the first date of the month <span class="lia-unicode-emoji" title=":smiling_face_with_smiling_eyes:">ðŸ˜Š</span>. Just a quick add-on question: could you please recommend some books that I could read to achieve a deep understanding of DAX like yours? Thanks</SPAN></P>Mon, 21 Jun 2021 02:39:23 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/How-can-DAX-compare-a-column-to-itself/m-p/1910805#M41309nemo2021-06-21T02:39:23ZRe: How can DAX compare a column to itself
https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-can-DAX-compare-a-column-to-itself/m-p/1910835#M41311
<P><LI-USER uid="52518"></LI-USER> thanks for the suggestions. Much appreciated. </P>Mon, 21 Jun 2021 02:41:32 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/How-can-DAX-compare-a-column-to-itself/m-p/1910835#M41311nemo2021-06-21T02:41:32ZRe: How can DAX compare a column to itself
https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-can-DAX-compare-a-column-to-itself/m-p/1911782#M41320
<P><LI-USER uid="308234"></LI-USER> </P><P> </P><P>Yes, your understanding is correct. And yes, DAX formulas are counterintuitive to those who start their journey with this language. Because this language is different from anything one has seen before; it's not object-oriented and it's functional. To really understand it one has to be intimately familar with the notion of CONTEXTS and their interaction with other features of the language.</P><P> </P><P> </P><LI-CODE lang="csharp">// A much better formulation of your measure
// is this one:
[Previous Month Sales] =
var MaxDateVisibleInCurrentContext =
MAX( 'Date'[Calendar Month] )
var DateOneMonthAfter =
EDATE( MaxDateVisibleInCurrentContext, 1)
var Result =
CALCULATE(
[Sum of Sales],
'Date'[Calendar Month] = DateOneMonthAfter
)
return
Rasult
// This, in fact, is the standard way
// that it should be written. Don't be fooled
// by the boolean condition in the second
// argument of CALCULATE. All filters in CALCULATE
// are ALWAYS tables. What you see above is just
// syntactic sugar. The code above has at least
// 2 advantages over the other ones:
// 1) It's more readable and understandable at
// a glance and
// 2) it has the potential to be faster since
// such conditions like "T[Col] = Value" under
// CALCULATE have been optimized internally.</LI-CODE><P> </P><P> </P><P>As for the book... There's only one book to rule them all: "The Definitive Guide to DAX" by Alberto Ferrari and Marco Russo. Buy it, read it at least 3-4 times (you'll have to do it anyway, even without my nudging) and practise a lot. I use this site/these forums to actually test my knowledge and I tend to pick up the problems that others leave without answers. This is how I've hardened my DAX (and in fact Power BI) skills. Also, I always keep up to date with articles written and recorded by The Italians (Alberto and Marco). I frequently visit <A href="http://www.sqlbi.com" target="_blank" rel="noopener">www.sqlbi.com </A>since it's the corner of the Internet where all the knowledge of DAX ultimately resides.</P><P> </P><P>By the way, it doesn't matter how you write boolean conditions that contain "=". "a = b" is totally equivalent to "b = a". As far as I remember, this is an axiom of logic.</P>Mon, 21 Jun 2021 09:29:55 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/How-can-DAX-compare-a-column-to-itself/m-p/1911782#M41320daxer2021-06-21T09:29:55ZRe: How can DAX compare a column to itself
https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-can-DAX-compare-a-column-to-itself/m-p/1912166#M41338
<P>Hi <LI-USER uid="239341"></LI-USER>, another valuable advice. Thanks so much for sharing your knowledge.</P><P> </P><P>Yes, totally agreed that the new version of [Previous Month Sales] is much better to read and understand.</P><P> </P><P>I'm not sure syntactic sugar is a good idea because it would fool people (yes, I mistakenly thought the second argument of CALCULATE was a boolean condition, thanks for pointing that out) and obscure the logic (thus making DAX formulas counterintuitive).</P><P> </P><P>Yes, you're right: if A = B then B = A. But when I said it should have been A = B instead of B = A, I was thinking of <STRONG>assigning</STRONG> a known value B to a variable A rather than <STRONG>comparing</STRONG> between two values A and B.</P>Mon, 21 Jun 2021 11:55:56 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/How-can-DAX-compare-a-column-to-itself/m-p/1912166#M41338nemo2021-06-21T11:55:56Z