DAX Measure to count how many players played all Games [ 3 Games or 2 Games or 1 Games]

[Anonymous]

Hello Members

I'm trying to calculate DAX Measure to count how many players played all 3 Games or 2 Games or 1 Games [sample datasets attached]. Please note the Distinct Count Games is DAX formula. Please help me in solving this puzzle. The final out put should look something like this [Below mentioned]. Lastly the column header should like this 1 Game || 2 Game || 3 Game 

[wdx223_Daniel]

@Anonymous 

say you have created a dimension table,NewDimTable, which have two columns,. Category column has 1 Game || 2 Game || 3 Game, and Index column has 1,2,3.

then draw the column of Category into column of visual, and put below measure in the value area.

=VAR _games=MAX('NewDimTable'[Index]) RETURN COUNTROWS(FILTER(ALL('FactTable'[Persion ID),[DistinctCount Game]=_games))

[Fowmy]

@Anonymous 

You can create a support table and link on the Distinct Count Column show in Matrix.

You can download the file: HERE

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

thanks @Fowmy

as mentioned the distinct count is DAX measure not a standalone table.  so the distinct count of team in my table from my orginal mail arrived from DAX. hope that clarify. 

[Fowmy · Accepted solution]

@Anonymous 

Please check the file now: 

You can download the file: HERE

Replace your Distinct Count Games measure with mine.

Game Count Measure = 
var _Count = SELECTEDVALUE( 'Game Count'[Count] ) return

SUMX(
    VALUES(Games[Person ID]),
    IF( [Distinct Count Games]  = _Count , 1 )
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

Thank you @Fowmy -  it worked brilliantly. Much appreciated.