locked
Why are the following two measures not identical? RRS feed

  • 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

Answers

  • 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 greggyb Monday, October 5, 2015 4:52 PM typo
    • Marked as answer by JP3O Monday, October 5, 2015 8:02 PM
    Monday, October 5, 2015 4:52 PM