Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Icad
Frequent Visitor

Many REMOVEFILTERS. Is there a shorter way?

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.
1 ACCEPTED SOLUTION

you can then use allexcept()

 

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
vanessafvg
Super User
Super User

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

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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.

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... 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

CST, thank you for your help 🙂

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

you can then use allexcept()

 

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.