cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cturner
Helper I
Helper I

filter dimension A by distinct count of dimension B per distinct dimension A

My data contains (among others) columns CLIENT and AGENCY.  I want to count the number of distinct values for CLIENT that have, per distinct value of CLIENT, distinct counts of AGENCY greater than or equal to two.  This seems like it should be straightforward, but I'm clearly missing something in the following DAX:

 

calculate(DISTINCTCOUNT('data'[CLIENT]),filter(all('data'[CLIENT]),DISTINCTCOUNT('data'[AGENCY])>=2)

 

Can someone kindly point out what I'm missing?

1 ACCEPTED SOLUTION
mattbrice
Solution Sage
Solution Sage

Measure =
CALCULATE (
    DISTINCTCOUNT ( 'data'[CLIENT] ),
    FILTER (
        ALL ( 'data'[CLIENT] ),
        CALCULATE ( DISTINCTCOUNT ( 'data'[AGENCY] ) )>= 2 )
)

Give this a try.  

View solution in original post

2 REPLIES 2
mattbrice
Solution Sage
Solution Sage

Measure =
CALCULATE (
    DISTINCTCOUNT ( 'data'[CLIENT] ),
    FILTER (
        ALL ( 'data'[CLIENT] ),
        CALCULATE ( DISTINCTCOUNT ( 'data'[AGENCY] ) )>= 2 )
)

Give this a try.  

View solution in original post

Thanks, that got it.  Didn't quite get the second calculate step, but it makes sense now.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.