Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
hobosapien
Frequent Visitor

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

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
1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@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])
 
11.PNG




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
v-kaiyue-msft
Community Support
Community Support

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.

vkaiyuemsft_0-1718332908617.png

 

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

vkaiyuemsft_1-1718332942933.png

 

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
Super User
Super User

@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])
 
11.PNG




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.