Forum Discussion

SanjeevKumar's avatar
SanjeevKumar
Frequent Visitor
4 years ago
Solved

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

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's avatar
    SanjeevKumar
    4 years ago

    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

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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

  • SanjeevKumar's avatar
    SanjeevKumar
    Frequent Visitor
    4 years ago

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

    Thanks in advance

    • SanjeevKumar's avatar
      SanjeevKumar
      Frequent Visitor
      4 years ago

      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