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
plancaster88
Regular Visitor

Opposite of ALLSELECTED? (ALLNOTSELECTED)

Hello,

I was wondering if anyone knew a way to calculate a measure for all values in a column outside of your current filters. This would be called ALLNOTSELECTED if it were an actual DAX formula.

 

ALLSELECTED Example:= Calculate([Measure], Allselected(table[column]))

ALLNOTSELECTED  Example (This is what I want):= Calculate[Measure], Allnotselected(table[column]))

 

Creating an ALL minus Current Selection measure is not an option because the measure has to do with # of members using a product. The slicers have to do with region and a member could switch regions throughout the year causing an incorrect subtraction.

1 ACCEPTED SOLUTION

Sure,

Are the various groups that you might filter on all in the same table (I'll call it Region)?
In that case you can pass the entire Region table to ALL/ALLSELECTED:

 

ALLNOTSELECTED Measure =
CALCULATE (
    [Measure],
    EXCEPT ( ALL ( Region ), ALLSELECTED ( Region ) )
)

 

If you have child groups in related tables (e.g. Subregion is on the 1 side of a relationship with Region), a more robust version of this measure would be needed in order to clear filters on any table related to Region:

ALLNOTSELECTED Measure =
CALCULATE (
    [Measure],
    EXCEPT ( ALL ( Region ), ALLSELECTED ( Region ) ),
    ALL ( Region )
)

 

Have a play with those and see if they work for you.

 

Cheers,

Owen 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

Maybe you can use ALL less ALLSELECTED?

That won't work in my situation because I am trying to get a distinct member count where members can be in seperate regions throughout a year.

 

Example: I am looking at data for 1 year and want to find distinct members outside of my current selection (selection being location slicers). Members can move from 1 region to the next in the middle of the year.

 

Scenario... Region A has 10 distinct members and Region B has 10 distinct members for 1 years worth of data. However, 2 members were located in both regions at some point in the year. This means there were 18 distinct members between the 2 regions. If I had one region selected and applied an ALL less ALLSELECTED it would give me 18 - 10 = 8. I need it to give me the correct number outside of the current selection which would be 10 (not 8).

 

 

 

@plancaster88

 

That's right, you can't just subtract two measures to get the result you want.

 

You should use the EXCEPT function to get the complement of ALLSELECTED(table[column]) and use that as a filter argument.

 

Something like:

ALLNOTSELECTED Measure =
CALCULATE (
    [Measure],
    EXCEPT ( ALL ( table[column] ), ALLSELECTED ( table[column] ) )
)

Cheers,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Thanks, Owen! That works if I only have one slicer. You wouldn't happen to know how I could make this method work if I had more than one slicer? I would want this to apply if the child groups were sliced as well(Region > Sub-region > etc..)

Sure,

Are the various groups that you might filter on all in the same table (I'll call it Region)?
In that case you can pass the entire Region table to ALL/ALLSELECTED:

 

ALLNOTSELECTED Measure =
CALCULATE (
    [Measure],
    EXCEPT ( ALL ( Region ), ALLSELECTED ( Region ) )
)

 

If you have child groups in related tables (e.g. Subregion is on the 1 side of a relationship with Region), a more robust version of this measure would be needed in order to clear filters on any table related to Region:

ALLNOTSELECTED Measure =
CALCULATE (
    [Measure],
    EXCEPT ( ALL ( Region ), ALLSELECTED ( Region ) ),
    ALL ( Region )
)

 

Have a play with those and see if they work for you.

 

Cheers,

Owen 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Thanks @OwenAuger .  Exactly what I was looking for to solve my issue.

That worked like a charm! Thanks so much. I've been trying to figure that one out for a while.

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.