locked
About change filter context in a measure RRS feed

  • Question

  • Hi team,

    Currently I am using power pivot 2010 due to the security reason from my silly working place.

    As a result, I am not able to use variable in DAX calculation and it is really painful.

    The data model look like this in this case:


    It is really simple: A date table and a sales table, which linked by date month.

    In order to get the running total sales figure by the month, I created a pivot table and a measure called MTD Sales

    The way to calculate MTD sales is to remove existing filter contexts in pivot table (date[Type] & date[Month]), and apply new filter context (smaller or equals to the max date[Seq] into the calculation and get the figure:

    This does work and give me the figure I am looking for. However, I do have a question when I try to reference the max date[seq] in this calculation. In the formulas of this measure, by using all in the filter part, it removes all the filters on date table. Consequently, the maximum date[seq] should be 12 and the running total calculation should fail. Interestingly, by using max(date[seq]) the calculation works. 

    My question is, by using

    Filter

    (

    all(date),

    date[seq] (ps: first date[seq])

    <=

    max(date[seq]) (ps: second date[seq])

    ), 

    the first date date[seq] should refer to all the contents of date table. Why the second date[seq] refers to the previous filter contexts of the pivot table instead of following same rule of the first date[seq] (all contents of date table)?

    Hope I did not confuse you.

    Cheers,

    Tom Sun

    Monday, September 25, 2017 12:58 AM

Answers

  • Hi Tom,

    what All does is ignoring the current filter context and always returns the whole table. And it does nothing more than that: So all other parts of the formula will be evaluated within the existing filter context. (See page 94 of Russo/Ferrari "The definite guide to DAX")


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by Tom J Sun Monday, October 2, 2017 11:26 PM
    Sunday, October 1, 2017 9:02 AM
    Answerer

All replies

  • Hi team,

    Is there anyone can help me with this?

    Cheers,

    Tom Sun

    Tuesday, September 26, 2017 12:39 AM
  • Hi Tom, 

    Thanks for your question.

    MAX(date[seq]) is a measure expression , a measure will apply current row context and filter context, thus, you will get the correct figure.

    For more information about Row Context and Filter Context in DAX , please refer to below blog:
    https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, September 26, 2017 4:59 AM
  • Hi Wilson,

    I read the article while it does not answer my question.

    Could you please explain a little bit more on this case?

    Cheers,

    Tom Sun

    Tuesday, September 26, 2017 6:46 AM
  • Hi Wilson,

    The other thing I don't understand is what does it mean by "MAX(date[seq]) is a measure expression "?

    Cheers,

    Tom Sun

    Tuesday, September 26, 2017 6:49 AM
  • Hi Tom,

    what All does is ignoring the current filter context and always returns the whole table. And it does nothing more than that: So all other parts of the formula will be evaluated within the existing filter context. (See page 94 of Russo/Ferrari "The definite guide to DAX")


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by Tom J Sun Monday, October 2, 2017 11:26 PM
    Sunday, October 1, 2017 9:02 AM
    Answerer
  • Hi Imke,

    Thanks for the explanation.

    I will check the book as you mentioned about.

    Cheers,

    Tom Sun

    Monday, October 2, 2017 11:27 PM