Forum Discussion

Jeanxyz's avatar
Jeanxyz
Power Participant
5 years ago
Solved

difference between removefilters(), all()

Hi, I'm confused with the use of all() and removefilters(). I wonder if someone can give me an explanation about the two calendar filters: all() vs removefilters().   I have written two measures u...
Need Help
  • Jeanxyz's avatar
    Jeanxyz
    Power Participant
    5 years ago

    Thanks a lot, Fowmy! As usual, you gave me good guidance.

     

    I did a test with a small sales fact table as below and link it to dim_date table. I also created three measures using removefilters(), all(), var respectively. It turns out all() and removefilters() generate the same results, both remove the filter in [Date] column, but if there is filter applied to another column in the Dim_Date table, [Date] field is still filtered. E.g., if I filter year 2021, then only dates in year 2021 are included in measure 1 and measure 2 , dates in year 2020 are not considered.

     

    The last measure however works quite differently, the var DatesSelectionAndEarlier actually ignores any filters related to dim_Date table and calculate dates from the very 1st day in the dim_date table till the last day in the dim_date table.

     

    So the learning here is var functions work seperately from current filtering context and start from the whole table.

     

     

     

     

    1. sales removefilters = calculate([sales],removefilters(Dim_Date[Date]))
    2. sales all = calculate([sales],all(Dim_Date[Date]))
    3. sales from day1 till maxdate =

    VAR DatesSelectionAndEarlier =
    FILTER (
    All ( 'Dim_Date'[Date] ),
    'Dim_Date'[Date]
    <= MAX ( 'Dim_Date'[Date] )
    )
    var SalesAmt = CALCULATE([sales],DatesSelectionAndEarlier)
    return
    SalesAmt

     

     

     

    • Fowmy's avatar
      Fowmy
      Super User
      5 years ago

      Thanks,

      So did it solve the problem and your query? 🙂