DAX to calculate count distinct players based max score

[Anonymous]

Hi All,

I need some help to build dax query. 

I have a table like this

day	player	score
23/jun	1	3
23/jun	2	3
23/jun	3	4
24/jun	2	4
25/jun	1	5

 
I want to calculate a measure that depending on the date filter, retrieves the count distinct of players with max score, something like this:
Date filter: between 23 and 24of jun

score	countdistinct(player) where max score equal to
1	0
2	0
3	1
4	2
5	0

or

Date filter: between 23 and 25of jun

score	countdistinct(player) where max score equal to
1	0
2	0
3	0
4	2
5	1

This was my inicial measure query, but the results aren't accurate...

# Users achieved max score =

VAR MAXScore = MAX('Table'[Score])

VAR COUNTDIST = DISTINCTCOUNT('Table'[player])

RETURN

CALCULATE(COUNTDIST, MAXSTREAK='Table'[score])

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please refer to my pbix file to see if it helps you.

Create a table first.

Table 2 = SUMMARIZE('Table','Table'[ score])

Then create two measures.

Measure =
VAR _maxdate =
    CALCULATE ( MAX ( 'Table'[day] ), ALLSELECTED ( 'Table' ) )
VAR _mindate =
    CALCULATE ( MIN ( 'Table'[day] ), ALLSELECTED ( 'Table' ) )
VAR _countrows =
    CALCULATE (
        MAX ( 'Table'[ score] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[ player] = SELECTEDVALUE ( 'Table'[ player] )
                && 'Table'[day] >= _mindate
                && 'Table'[day] <= _maxdate
        )
    )
RETURN
    _countrows

Measure_2 =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[ player] ),
    FILTER ( ( 'Table' ), [Measure] = SELECTEDVALUE ( 'Table 2'[ score] ) )
)

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi , i am not sure but this may be help you

Measure 2 = 
var _table=SUMMARIZE('Table','Table'[player],'Table'[Date],"distinctcount",DISTINCTCOUNT(MAX('Table'[score])))
return
SUMX(_table,[distinctcount])