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? 🙂