DAX - Monthly leader board

[cottrera]

Hi

I have a table that contains the results from a customer survey , based on the engineers visit.  I need to create a monthly leader board to show the best monthtly rated engineer.

Facts survey table - connected to dates table

Engineer	Survey Ref	Survey Date	Rating	Out of
Engineer 1	S1	01/01/2022	4	5
Engineer 1	S2	02/01/2022	4	5
Engineer 1	S3	03/01/2022	5	5
Engineer 1	S4	04/01/2022	5	5
Engineer 1	S5	01/02/2022	5	5
Engineer 1	S6	02/02/2022	5	5
Engineer 1	S7	03/02/2022	5	5
Engineer 1	S8	04/02/2022	5	5
Engineer 2	S9	01/01/2022	5	5
Engineer 2	S10	02/01/2022	5	5
Engineer 2	S11	03/01/2022	5	5
Engineer 2	S12	04/01/2022	5	5
Engineer 2	S13	01/02/2022	3	5
Engineer 2	S14	02/02/2022	3	5
Engineer 2	S15	03/02/2022	4	5
Engineer 2	S16	04/02/2022	5	5

Based on the small sample table above. These would be the results I would expect to see

Monthly Highest Rating Engineer Leader board

Jan	Feb
Engineer 2	Engineer 1

Does anyone have any idears how to do this with a DAX function.

thank you

Richard

[SpartaBI · Accepted solution]

@cottrera 
Create this measure:

Measure = 
VAR _tbl = 
ADDCOLUMNS(
    VALUES(survey[Engineer]),
    "@Total Rating", CALCULATE(SUM(survey[Rating ]))
)
VAR _max_rating = MAXX(_tbl, [@Total Rating])
VAR _top_tbl = 
    FILTER(
        _tbl,
        [@Total Rating] = _max_rating
    )
VAR _result = 
    CONCATENATEX(_top_tbl, survey[Engineer], ", ")
RETURN
    _result

link to file with the solution:
DAX - Monthly leader board.pbix

      

Showcase Report – Contoso By SpartaBI

[cottrera]

Thank you for the quick response 😀 Love the showcase report

[SpartaBI]

@cottrera thank you my friend! Please give it a kudos 😀
*Will appreciate also kudos for my messages here 😉