Reply
Highlighted
Frequent Visitor
Posts: 4
Registered: ‎04-20-2017
Accepted Solution

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.


Accepted Solutions
Established Member
Posts: 235
Registered: ‎02-29-2016

Re: Opposite of ALLSELECTED? (ALLNOTSELECTED)

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 Smiley Happy

View solution in original post


All Replies
Established Member
Posts: 193
Registered: ‎07-28-2015

Re: Opposite of ALLSELECTED? (ALLNOTSELECTED)

Maybe you can use ALL less ALLSELECTED?

Frequent Visitor
Posts: 4
Registered: ‎04-20-2017

Re: Opposite of ALLSELECTED? (ALLNOTSELECTED)

[ Edited ]

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

 

 

 

Established Member
Posts: 235
Registered: ‎02-29-2016

Re: Opposite of ALLSELECTED? (ALLNOTSELECTED)

[ Edited ]

@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

Frequent Visitor
Posts: 4
Registered: ‎04-20-2017

Re: Opposite of ALLSELECTED? (ALLNOTSELECTED)

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

Established Member
Posts: 235
Registered: ‎02-29-2016

Re: Opposite of ALLSELECTED? (ALLNOTSELECTED)

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 Smiley Happy

Frequent Visitor
Posts: 4
Registered: ‎04-20-2017

Re: Opposite of ALLSELECTED? (ALLNOTSELECTED)

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