Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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 🙂
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).
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
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 🙂
That worked like a charm! Thanks so much. I've been trying to figure that one out for a while.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |