The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 |
Solved! Go to Solution.
pls try to create a new table
Proud to be a Super User!
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.
pls try to create a new table
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
57 | |
33 | |
20 | |
18 | |
15 |
User | Count |
---|---|
44 | |
35 | |
32 | |
23 | |
23 |