Many REMOVEFILTERS. Is there a shorter way?

[Icad]

Hello DAX experts,

That is my first question here 🙂

I have a measure with many REMOVEFILTERS:

Store Vals = 

CALCULATE(SUM(pbiFact[Sale]),
  REMOVEFILTERS('pbiDepartments'[Department]),
  REMOVEFILTERS('pbiSubCategories'[SubCategory]),
  REMOVEFILTERS('pbiCategories'[Category]),
  REMOVEFILTERS('pbiProducts'[Product])
)

That I use in another measure:

% of Stores Vals =
DIVIDE( [Vals],
  CALCULATE ( [Store Vals] ) ,
  BLANK()
)

Is there a shorter way to write the first measure?

Thanks. Icad.

[vanessafvg]

can i first ask what you actually trying to end up with.  Are you just wanting to sum the value filter free?  ie you want all to divide into?

I think what you looking to do is use the all function.

% of Stores Vals =
DIVIDE (
    [Vals],
    CALCULATE ( SUM ( pbiFact[Sale] ), ALL ( [pbiFact] ) ),
    BLANK ()
)

this video might help.  

https://www.youtube.com/watch?v=AIk1vPM-u2I

[Icad]

Vanessa, thanks for your fast answer.

ALL remove all filters. I need to keep filters (Year,..., YTD)

and just remove Hierarchy filters (like Dept, Cat, SubCat, Product).

I thought there is something like:

CALCULATE ( SUM ( pbiFact[Sale] ),  keep row context and all dates filters except hierarchy filters )

Thanks. Icad.

[v-xuding-msft]

Hi @Icad ,

As vanessafvg suggests, you could try the function of ALLEXCEPT. Here are the blogs that you could reference to learn more.

REMOVEFILTERS() in DAX 

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept... 

[Icad]

CST, thank you for your help 🙂

I'll try again later and post here. Icad.

[vanessafvg · Accepted solution]

you can then use allexcept()

https://docs.microsoft.com/en-us/dax/allexcept-function-dax

[Icad]

Vanessa, sorry for the delay (bad times).

ALLEXCEPT doesn't work for me, maybe because I'm too tired.

I'll try again later and let you know.

Thanks again for your valuable time. Icad