Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JD0963
Helper I
Helper I

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

New Capture.PNG

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

1 REPLY 1
Greg_Deckler
Super User
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.