Forum Discussion

JDanielHarvey's avatar
JDanielHarvey
Regular Visitor
6 years ago
Solved

FILTER with ALL with External Filter Context

Hoping to get some clarity about how the ALL() (aka removefilter) function behaves inside FILTER() when using a measure in a chart that should be removing external filter context be it a pivot table or a chart.
I found this formula used in a video from Sam McKay in this video where he attempts to describe what is happening as: "The max date is always going to equal to the actual date. That's why we can create this running total.". His definition of actual date show's his cursor pointing to external/evaluation context from a table in a canvas report. My understanding is the ALL() function should be removing the external filter context but that doesn't seem to be the case with the formula below used in the video I provided a link to:

 

30 Day Running Total =
CALCULATE( [Total Sales],
    FILTER( ALL( Dates ),
        Dates[Date] > MAX( Dates[Date] ) - 30 &&
        Dates[Date] <= MAX( Dates[Date] ) ) )

Worth noting, I have reviewed Alberto Ferrari's article on the ALL() function here. I also ran the formula through DAX Studio where the results provide 30 rows of every column in the date table from the last date in the report (12/29/2017) Sam McKay uses in the video.  
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi JDanielHarvey ,

     

    Sorry for our delay in response, the measured you provided works as expected, when we change the range of date by using slicer, it can show result based on all date.

     

     

    For example ,when no filter applied, for point 2020-4-1,  the result of this measure is [total sales] between 2020-3-2 and 2020-4-1

    if we change slicer start from 2020-3-31, for point 2020-4-1,  the result of this measure is still [total sales] between 2020-3-2 and 2020-4-1, the All(Date) will get all the rows of date table regardless of any filter or slicer applied, but the max('Dates'[Date]) get the current date 2020-4-1.


    If it doesn't meet your requirement, Could you please show the exact expected result based on the tables that we have shared? If you have any other questions, please kindly ask here and we will try to resolve it.


    By the way, PBIX file as attached.


    Best regards,

     

2 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi JDanielHarvey ,

     

    Sorry for our delay in response, the measured you provided works as expected, when we change the range of date by using slicer, it can show result based on all date.

     

     

    For example ,when no filter applied, for point 2020-4-1,  the result of this measure is [total sales] between 2020-3-2 and 2020-4-1

    if we change slicer start from 2020-3-31, for point 2020-4-1,  the result of this measure is still [total sales] between 2020-3-2 and 2020-4-1, the All(Date) will get all the rows of date table regardless of any filter or slicer applied, but the max('Dates'[Date]) get the current date 2020-4-1.


    If it doesn't meet your requirement, Could you please show the exact expected result based on the tables that we have shared? If you have any other questions, please kindly ask here and we will try to resolve it.


    By the way, PBIX file as attached.


    Best regards,

     

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi JDanielHarvey ,

     

    How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


    Best regards,