Need help in DAX

[Anonymous]

Hello Everyone, 

Please can some help me with the below logic uisng the DAX funnction or M-query. 

Note the below input is using the direct query mode report

Current Table :

Match ID	Category
123457	Climate Environment
316789	Climate Environment
215789	Climate Environment
123457	Climate Environment
123457	Climate Environment
215789	Geo Climate
215789	Geo Climate

Need the Output

Match ID	Category	Distinct count of Match ID by Category
123457	Climate Environment
316789	Climate Environment
215789	Climate Environment
123457	Climate Environment
123457	Climate Environment
215789	Geo Climate
215789	Geo Climate

[PaulOlding]

Hi @Anonymous 

This DAX is for a measure to get the distinct count

Distinct count of Match ID by Category = 
CALCULATE(
    DISTINCTCOUNT('Table'[Match ID]),
    ALLEXCEPT('Table','Table'[Category])
)

[Whitewater100 · Accepted solution]

Hello:

You can try this:  DISTINCTCOUNT =

 VAR _matchID = Current[MatchID}

   VAR _ Category = Current[Category]

Return
COUNTROWS(

                  FILTER(
                        ALL( ‘Current’),

Current[MatchID} = _matchID &&

Current[Category] = _Category)
)

I hope this helps you!