topic How can DAX compare a column to itself in DAX Commands and Tips

How can DAX compare a column to itself

nemo — Sun, 20 Jun 2021 01:01:51 GMT

I came across this formula:

Previous Month Sales =
CALCULATE(
[Sum of Sales],
FILTER(
ALL('Date'[Calendar Month]),

MAX('Date'[Calendar Month]) = EDATE('Date'[Calendar Month], 1)
)
)

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).

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)?

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?

Thanks in advance.

Re: How can DAX compare a column to itself

Fowmy — Sun, 20 Jun 2021 10:54:42 GMT

nemo

Let's say you are executing this measure against Month-Year in a Table visual. 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.
You can modify your measure as :

Previous Month Sales = CALCULATE( [Sum of Sales], FILTER( ALL('Date'), MAX('Date'[Date]) = EDATE('Date'[Date], 1) ) )

I suggest you utilized other standard methods with :

https://docs.microsoft.com/en-us/dax/dateadd-function-dax
https://docs.microsoft.com/en-us/dax/previousmonth-function-dax

Re: How can DAX compare a column to itself

Anonymous — Sun, 20 Jun 2021 18:28:53 GMT

[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.

Re: How can DAX compare a column to itself

nemo — Mon, 21 Jun 2021 02:39:23 GMT

Hi Anonymous, 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.

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"! 🙂

To ensure I've understood you correctly, this is my interpreation on how the calcuation takes place.

Assume there is a visual table with a row header 1-Feb-2018. Then

var MaxMonthVisibleInCurrentContext = MAX( 'Date'[Calendar Month] ) = "1-Feb-2013"

That value of 1-Feb-2013 is then used to figure out what is the value for CurrentlyIteratedMonth. That is,

EDATE( CurrentlyIteratedMonth, 1 ) = MaxMonthVisibleInCurrentContext = "1-Feb-2013" // Therefore: CurrentlyIteratedMonth = "1-Jan-2013"

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).

If my interpretation above is correct, then I think the syntax of the formula is counterintuitive. Because 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 EDATE('Date'[Calendar Month], 1) = MAX('Date'[Calendar Month]).

I'll wait for your confirmation of my clarification before accepting your (great) answer as the solution.

Warmest regards,

PS: I can see that you're very smart to make the right assumption that 'Date'[Calendar Month] is the first date of the month 😊. 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

Re: How can DAX compare a column to itself

nemo — Mon, 21 Jun 2021 02:41:32 GMT

Fowmy thanks for the suggestions. Much appreciated.

Re: How can DAX compare a column to itself

Anonymous — Mon, 21 Jun 2021 09:29:55 GMT

nemo

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.

// 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.

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 www.sqlbi.com since it's the corner of the Internet where all the knowledge of DAX ultimately resides.

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.

Re: How can DAX compare a column to itself

nemo — Mon, 21 Jun 2021 11:55:56 GMT

Hi Anonymous, another valuable advice. Thanks so much for sharing your knowledge.

Yes, totally agreed that the new version of [Previous Month Sales] is much better to read and understand.

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).

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 assigning a known value B to a variable A rather than comparing between two values A and B.