Hi everyone,

I've scoured the Internet and have been unable to find a DAX example of how to aggregate a measure for all members of a dimension EXCEPT the selected member(s). 

Certainly, I can create a measure for all members and subtract that from the measure with the selected member.  But this doesn't work for a distinct count.

I'm basically looking for something like the following (forgive this rough stab, I know it's wrong):

CALCULATE([Distinct Count], FILTER(ALL(MyDimension), MyDimension[DimensionID]<>**the selected member**))

Does anyone have experience with this?  I'd really appreciate some help.  Thanks!

After some more searching and a lot more thinking, I finally stumbled upon the answer.  It involves using the solution for a cumulative total, but instead of <=, you use <>.

IF( COUNTROWS( VALUES( MyDimension[DimensionID] ) = 1,
CALCULATE( [MyMeasure], VALUES( MyDimension[DimensionID] ), FILTER( ALL(MyDimension[DimensionID]), MyDimension[DimensionID] <> VALUES( MyDimension[DimensionID] ))),
BLANK() )

Here are a couple of blog posts with further info!

http://www.powerpivotpro.com/2011/03/the-magic-of-ifvalues/
https://javierguillen.wordpress.com/2011/12/13/rolling-sum-across-categories-that-are-not-dates/

• Marked as answer by Shawn A. _ 2 hours 21 minutes ago