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

I Have multiple Slicer Filters for Different CEO Types. Now I need to get the distinct count for the CEOs based on the multiple CEO selection.


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...

If I select B from CEO Minus 1 and B,E,F from CEO Minus 2 and H from CEO Minus 3 I have to show the distinct count as 4 in the Card.

 

 

Thanks in advance

Sanjeev

DAX
  • daXtreme's avatar
    daXtreme
    4 years ago
    [# CEO] =
// This measure will also work if you CEO's
// are in different tables instead of one, T.
var CeoMinus1 = values( T[CEO minus 1])
var CeoMinus2 = values( T[CEO minus 2])
... // means: add more of these if you need in the same fashion
var DistinctCeos = 
    SUMX(
        DISTINCT(
            union(
                CeoMinus1,
                CeoMinus2,
                ... // same as before...
            )
        ),
        1
    )
return
    DistinctCeos

3 Replies

  • daXtreme's avatar
    daXtreme
    Icon for Solution Sage rankSolution Sage
    4 years ago
    [# CEO] =
// This measure will also work if you CEO's
// are in different tables instead of one, T.
var CeoMinus1 = values( T[CEO minus 1])
var CeoMinus2 = values( T[CEO minus 2])
... // means: add more of these if you need in the same fashion
var DistinctCeos = 
    SUMX(
        DISTINCT(
            union(
                CeoMinus1,
                CeoMinus2,
                ... // same as before...
            )
        ),
        1
    )
return
    DistinctCeos
    • SanjeevKumar's avatar
      SanjeevKumar
      Frequent Visitor
      4 years ago

      Hello daXtreme 

      This time I have different requirements for the same data. After so many different trials I am posting my query again here. Can you please provide the solution? 

      We have the CEO hierarchy as below and showing all these 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, "A", "B" from CEO Minus 1 and "B", "E", "F" from CEO Minus 2. We should get the distinct count as 3 from CEO Minus 2 slicer.

      Thanks in advance.

      Sanjeev