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 3 years, 3 months ago.

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

    • 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,


    You must be logged in to view attached files.

    • Started: 7
    • Replies: 2552
    • Total: 2559


    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.


    • Started: 2
    • Replies: 2
    • Total: 4


    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.