Distinct Count & Group by based on slicer selection?

[JD0963]

Hi, I have a table that looks like the attached image. I also have a slicer filter with the selectables "Division, Department and Team"

Basically when one of these selectables is selected I want to build a measure that will do a distinct count of ClientKey for whatever was selected. So for example, if Division was selected then it would show Eng as having 5 distinct clients, Sco having 2. If Team was selected it would show North having 2 distinct client and South having 5. If department was selected it would show General as having 7.

Does this make sense?

Thanks for any help

[Greg_Deckler · Accepted solution]

Perhaps something along these lines:

Distince Count = 
IF(
    HASONEVALUE('#Choices'[Choice]),
    SWITCH(
            VALUES('#Choices'[Choice]),
                "Division",
                        COUNTROWS(SUMMARIZE('#AATable','#AATable'[Division],'#AATable'[ClientKey])),
                "Department",
                        COUNTROWS(SUMMARIZE('#AATable','#AATable'[Department],'#AATable'[ClientKey])),
                "Team",
                        COUNTROWS(SUMMARIZE('#AATable','#AATable'[Team],'#AATable'[ClientKey]))
    ),
    COUNTROWS(SUMMARIZE('#AATable','#AATable'[Division],'#AATable'[ClientKey]))
)