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

    Thanks daXtreme for your valuable time in providing the solution.


daXtreme's avatar
daXtreme
Solution 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