topic Re: Need Help: How to get the Distinct Count value based on the multiple Slicer Filters selection in DAX Commands and Tips

Need Help: How to get the Distinct Count value based on the multiple Slicer Filters selection

SanjeevKumar — Thu, 11 Aug 2022 11:07:08 GMT

We have the CEO hierarchy as below and showing all the CEO types as different slicers.

CEO->CEO Minus 1->CEO Minus 2->CEO Minus 3->CEO Minus 4->CEO Minus 5->CEO Minus 6->CEO Minus 7->CEO Minus 8->CEO Minus 9

If I selected the combination of the lower Hierarchy CEO type and upper Hierarchy CEO types, I should get only the distinct count for the lower Hierarchy CEO type.

Ex:
CEO - A
CEO Minus 1 = A,B,C,D
CEO Minus 2 = B,E,F,G
CEO Minus 3 = E,F,H,I,J and so on...

Suppose we have selected the combination of

"A" from CEO slicer,

"A", "B" from CEO Minus 1 slicer and

"B", "E", "F" from CEO Minus 2 slicer.

We should get the distinct count as 3 from CEO Minus 2 slicer.

Thanks in advance.

Sanjeev

Re: Need Help: How to get the Distinct Count value based on the multiple Slicer Filters selection

lbendlin — Thu, 04 Aug 2022 20:02:11 GMT

Any particular reason for not using hierarchies and the PATH* functions?

Re: Need Help: How to get the Distinct Count value based on the multiple Slicer Filters selection

Anonymous — Tue, 09 Aug 2022 06:33:00 GMT

Hi SanjeevKumar,

AFAIK, current power bi slicers use 'AND' logic to link different filters.
If you want to achieve the custom filter effect, please consider breaking the current relationship mapping and write a measure to extract the selection to apply the filter effect on the 'visual level filter'.

Applying a measure filter in Power BI - SQLBI

Regards,

Moonlight

Re: Need Help: How to get the Distinct Count value based on the multiple Slicer Filters selection

SanjeevKumar — Wed, 10 Aug 2022 23:37:45 GMT

I Tried all the work arounds and unfortunately not suceeded with the solution. Please help me on the solution.

Thanks in advance

Re: Need Help: How to get the Distinct Count value based on the multiple Slicer Filters selection

SanjeevKumar — Thu, 11 Aug 2022 11:09:22 GMT

Finally after many work arounds I resolved this with switch function.

Intially I tried the switch function with CEO->CEO Minus 1->CEO Minus 2->CEO Minus 3->CEO Minus 4->CEO Minus 5->CEO Minus 6->CEO Minus 7->CEO Minus 8->CEO Minus 9

Later I realised the logic behind the switch function and reversed the CEO types to
CEO Minus 9->CEO Minus 8->CEO Minus 7->CEO Minus 6->CEO Minus 5->CEO Minus 4->CEO Minus 3->CEO Minus 2->CEO Minus 1->CEO

Var vAnySelectedCEO = ISFILTERED('dAccounts(PublicData)'[CEO])

Var vAnySelectedCEO1 = ISFILTERED('dAccounts(PublicData)'[CEO Minus 1])

Var vAnySelectedCEO2 = ISFILTERED('dAccounts(PublicData)'[CEO Minus 2])

Var vAnySelectedCEO3 = ISFILTERED('dAccounts(PublicData)'[CEO Minus 3])

Var vAnySelectedCEO4 = ISFILTERED('dAccounts(PublicData)'[CEO Minus 4])

Var vAnySelectedCEO5 = ISFILTERED('dAccounts(PublicData)'[CEO Minus 5])

Var vAnySelectedCEO6 = ISFILTERED('dAccounts(PublicData)'[CEO Minus 6])

Var vAnySelectedCEO7 = ISFILTERED('dAccounts(PublicData)'[CEO Minus 7])

Var vAnySelectedCEO8 = ISFILTERED('dAccounts(PublicData)'[CEO Minus 8])

Var vAnySelectedCEO9 = ISFILTERED('dAccounts(PublicData)'[CEO Minus 9])

Var vResult =

SWITCH(

TRUE(),

vAnySelectedCEO9 = TRUE(), vCEO9distinct,

vAnySelectedCEO8 = TRUE(), vCEO8distinct,

vAnySelectedCEO7 = TRUE(), vCEO7distinct,

vAnySelectedCEO6 = TRUE(), vCEO6distinct,

vAnySelectedCEO5 = TRUE(), vCEO5distinct,

vAnySelectedCEO4 = TRUE(), vCEO4distinct,

vAnySelectedCEO3 = TRUE(), vCEO3distinct,

vAnySelectedCEO2 = TRUE(), vCEO2distinct,

vAnySelectedCEO1 = TRUE, vCEO1distinct,

vAnySelectedCEO = TRUE, vCEOdistinct,

BLANK()

)

and it solved the issue.

Thanks for those who are looking into this solution.

~Sanjeev