# Why are the following two measures not identical?

• ### Question

• I have the following measure

LastInventoryCountInTable:=SUMX(
dimItems
;CALCULATE(
SUM(factInventory[Quantity])
;FILTER(
ALL(dimCalendar)
;dimCalendar[Date] =
CALCULATE(
MAX(factInventory[Date])
;FILTER(
ALL(dimCalendar)
;dimCalendar[Date] <= MAX(dimCalendar[Date])
)
)
)
)
)

Why is that not the same as the following measure that is broken down into pieces:

LastInventoryCountInTable:=SUMX(
dimItems
;CALCULATE(
SUM(factInventory[Quantity])
;FILTER(
ALL(dimCalendar)
;dimCalendar[Date] = [LastInventoryDate]
)
)
)

LastInventoryDate:=CALCULATE(
MAX(factInventory[Date])
;FILTER(
ALL(dimCalendar)
;dimCalendar[Date] <= MAX(dimCalendar[Date])
)
)

I like the idea of breaking down problems into several measures. But I don't understand why the measures above aren't identical.

Monday, October 5, 2015 3:51 PM

• CALCULATE() turns row context into filter context. When you define [LastInventoryDate] as a measure, everything inside is calculated in the row context of the FILTER() in [LastInventoryCountInTable]. Thus, your MAX(dimCalendar[Date]) is not evaluated in the pivot table filter context, but in the row context of the FILTER(ALL(dimCalendar),...).

This means that the MAX() in [LastInventoryDate] will always be equal to the current row in the row-by-row iteration of the FILTER() in [LastInventoryCountInTable].

I thought about this when writing the measures in your other thread. It turns out to be a quite difficult problem, and I opted not to try too hard on that, since the measures work without the abstraction of [LastInventoryDate] as a separate measure.

While I agree with your preference for modularity, in this case I make the judgment call that the elegance isn't worth the cost. It's not something I can easily identify a solution for. I wish you luck if you try to go further down this path, and apologize for the unsatisfying answer.

GNet Group BI Consultant

• Edited by Monday, October 5, 2015 4:52 PM typo
• Marked as answer by Monday, October 5, 2015 8:02 PM
Monday, October 5, 2015 4:52 PM