Home Forums Power Pivot Calculated measure not picked up up in filter criteria

This topic contains 2 replies, has 2 voices, and was last updated by  J_buchanan 2 years, 7 months ago.

Viewing 3 posts - 1 through 3 (of 3 total)
  • Author
    Posts
  • #2162

    J_buchanan
    Participant
    • Started: 2
    • Replies: 2
    • Total: 4

    I am trying to create a measure that shows the order amount by City on the last order date then roll the MAX(last order date) order amount up to the State level.  Given the following measures, the last measure [Last Order Date Amount] is not respecting the [Max Last Order Date] measure.

    Max Last Order Date:=MAX(sales[Last Order Date]) = 10/22/2015

    Order Amount:=sum(sales[Last Order Amount]) = 135,837

    Last Order Date Amount:=CALCULATE([Order Amount],FILTER(sales,sales[Last Order Date]=[Max Last Order Date])) = 135,837

     

    I was only able to get it to work by dropping the [Max Last Order Date] measure and calculating the max date in the formula:

    Last Order Date Amount2:=CALCULATE([Order Amount],FILTER(sales,sales[Last Order Date]=MAX(sales[Last Order Date]))) = 626

     

    Am I wrong in thinking the first method should have worked?  The sample file is attached.

    Thank you,

    John

    Attachments:
    You must be logged in to view attached files.
    #2168

    tomallan
    Keymaster
    • Started: 9
    • Replies: 2244
    • Total: 2253

    John,

    This one was definitely a brain-buster for me to resolve (or at least to find out what was going on).

    For background, the FILTER function is an iterator and iterators create a “row context” and do something “special” with measures. Marco Russo, co-author of “Building Data Models with Power Pivot” said way to the bottom of a blog that you here can find here that “when you have a row context (such as created by the FILTER function in this topic) and you call a measure, you have an implicit CALCULATE statement wrapping the measure expression that transforms the row context into a filter context”.

    Since the context transition created by FILTER evaluated the [Max Last Order Date] measure only from one row at a time (the current row), all rows met the filter criteria.

    In “Last Order Date Amount2”, since “sales[Last Order Date]=MAX(sales[Last Order Date]” was not a measure, filter context was not changed.

    #2171

    J_buchanan
    Participant
    • Started: 2
    • Replies: 2
    • Total: 4

    Tom,

    Very interesting and good to know.  Thank you for your time figuring this out and for sharing the link to the DAX calculate article.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.