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

[SanjeevKumar]

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

[SanjeevKumar]

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

Thanks in advance

[SanjeevKumar · Accepted solution]

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

[Anonymous]

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

[lbendlin]

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