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 using different calendar filters, in the visual part, I have selected YearMonth in the column filter. In the page filter, I have selected year 2021. The date filters in the measures, page filter and column fields use the same calendar table.

 

I have put my question in the measure. The visual in attached on the bottom. Thanks for the help in advance.

 

Company Currency Amount Base_BS =
VAR BS =
FILTER(VALUES('Dim_Group GL Account'[Level 0]),'Dim_Group GL Account'[Level 0] = "BS" )
VAR DatesSelectionAndEarlier =
FILTER (
ALL ( 'Dim_Date'[Date] ),
'Dim_Date'[Date]
<= MAX ( 'Dim_Date'[Date] )
)
var CompCurrAmt = CALCULATE(sum('Fact_GL Entries'[Company Currency Amount]),BS,DatesSelectionAndEarlier)
//Question: it looks like the DatesSelectionAndEarlier overrides the YearMonth context in the column field. Is that how all() function works? i.e., all() will always removes all the filters in the calendar table even if the all() function only asks to remove filter in the [Date] field?
var result1 = if(HASONEVALUE('Dim_Legal Entity'[Legal Entity ID]),CompCurrAmt,BLANK())
var result2 = DIVIDE(result1,[SelectedUOM])
return
result2
 
Company Currency Amount Base_BS2 =
VAR BS =
FILTER(VALUES('Dim_Group GL Account'[Level 0]),'Dim_Group GL Account'[Level 0] = "BS" )
var CompCurrAmt = CALCULATE(sum('Fact_GL Entries'[Company Currency Amount]),BS,removefilters(Dim_Date[YearMonth]) )
//here by removefilters(), I expect the measure will override YearMonth filter in the column field. However, it looks the YearMonth value in the column field still has an impact. I only get the total results to Jan. instead of all months in 2021.
 
var result1 = if(HASONEVALUE('Dim_Legal Entity'[Legal Entity ID]),CompCurrAmt,BLANK())
var result2 = DIVIDE(result1,[SelectedUOM])
return
result2
 
 

 

 

 

Need Help

5 Replies

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


  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    5 years ago

    Hi Jeanxyz 

    Have you solved this problem? If yes, could you kindly accept the answer helpful as the solution (or kindly share your solution ). so the others can find it more quickly.

     

     

    Best Regards,

    Community Support Team _Tang

  • powerbi_zone's avatar
    powerbi_zone
    Frequent Visitor
    4 years ago

    Hello Dear ,

    Its not as complicated as you may think 🙂

    Differences between ALL() and REMOVEFILTERS()

    REMOVEFILTERS is just an alias of ALL here, so it works in the same manner.

    Basically, using only ALL() returns a table including all rows, ignoring any filters that might have been applied.

    However, when ALL is used as a filter argument of CALCULATE / CALCULATETABLE, it behaves differently: it removes filters from the table and does not return a table.

    To eliminate this confusing behavior of ALL, REMOVEFILTERS was introduced to replace ALL when it is used inside CALCULATE.

    You can find more in this video.Hope it helps!