Grouping by and then ranking by top 1 for each year via DAX

[hobosapien]

I have a spotify listening dataset that spans a decade and I would like to list the top listened-to artist per year over that time period. The data looks a bit like this:

Date	| Track	| Artist
2019	| Artist1_Track1	| Artist1
2019	| Artist2_Track1	| Artist2
2019	| Artist3_Track1	| Artist3
2019	| Artist1_Track2	| Artist1
2020	| Artist4_Track1	| Artist4
2020	| Artist5_Track1	| Artist5
2020	| Artist4_Track2	| Artist4
2020	| Artist3_Track1	| Artist3
2020	| Artist4_Track3	| Artist4

The results would preferably be a table like the following. This would be for over a 10-year period but for the sake of this example I shortened it to 2 years.

Year	| Most Listened To Artist
2019	| Artist1
2020	| Artist4

[v-kaiyue-msft]

Hi @hobosapien ,

Your solution is great, @ryan_mayu . It worked like a charm! Here I have another idea in mind, and I would like to share it for reference. 

1. Group by date and artist columns in power query editor.

2. Create a calculated column in desktop.

Column =
CALCULATE(MAX('Table'[Count]),FILTER(ALL('Table'),'Table'[Date] = EARLIER('Table'[Date])))

3. Create a calculated table.

Table 2 =
VAR _table = FILTER('Table','Table'[Count] = 'Table'[Column])
RETURN
SELECTCOLUMNS(_table,"date",'Table'[Date],"artist",'Table'[| Artist])

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[ryan_mayu · Accepted solution]

@hobosapien 

pls try to create a new table

Table 2 =

var tbl=SUMMARIZE('Table','Table'[Date],'Table'[Artist],"count",count('Table'[Artist]))

var tbl2=FILTER(ADDCOLUMNS(tbl,"rank",rankx(FILTER(tbl,[Date]=EARLIER([Date])),[count],,DESC)),[rank]=1)

return SELECTCOLUMNS(tbl2,"Date",[Date],"Artist",[Artist])

[hobosapien]

Works wonderfully! You cannot understand the rabbit hole of RANKX and COUNT research I went down before I gave up. Much appreciated.