topic Re: Grouping by and then ranking by top 1 for each year via DAX in DAX Commands and Tips

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

hobosapien — Thu, 13 Jun 2024 23:39:44 GMT

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

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

ryan_mayu — Fri, 14 Jun 2024 01:20:02 GMT

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])

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

Anonymous — Fri, 14 Jun 2024 02:43:26 GMT

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.

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

hobosapien — Fri, 14 Jun 2024 22:11:12 GMT

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