Forum Discussion

JD0963's avatar
JD0963
Helper I
8 years ago
Solved

Distinct Count & Group by based on slicer selection?

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

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

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

1 Reply

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

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