Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
So basically im trying to select only the rows where each asset id has the max views
eg how to convert:
Asset ID | Article Title | views |
935605 | Traffic Updates: Collision on M50 southbound causing delays | 57 |
935605 | Latest Traffic: Multi-vehicle collision on the M4 | 130 |
935605 | Daily Traffic Updates: Heavy eastbound along the Grand Canal | 27 |
935605 | Latest Traffic: Rush hour busy with schools back | 50 |
935605 | Daily Traffic Updates: R352 closed after vehicle fire near Ennis | 44 |
1001467 | Nurmagomedov beats Conor McGregor but loses his head | 107 |
1001467 | Khabib beats Conor McGregor but loses his head | 23 |
to
Asset ID | Article Title | views |
935605 | Latest Traffic: Multi-vehicle collision on the M4 | 130 |
1001467 | Nurmagomedov beats Conor McGregor but loses his head | 107 |
Solved! Go to Solution.
Briefly:
Create a duplicate of your query.
On one query, choose 'group by' Asset Id, select 'Max' and views. This will give you a 2 column table.
Merge that query with the original, on both columns, choose 'inner join'.
Tidy up the result
Hi @StephenF,
By my tests, the solution from HotChilli should be useful.
In addition, you could add another aggregation to group the Asset ID so that you could achieve your desired output more easier.
If you have solved your problem, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If you still need help, please feel free to ask.
Best Regards,
Cherry
Hi @StephenF,
By my tests, the solution from HotChilli should be useful.
In addition, you could add another aggregation to group the Asset ID so that you could achieve your desired output more easier.
If you have solved your problem, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If you still need help, please feel free to ask.
Best Regards,
Cherry
Yes, this doesnt work Cherry, Max on Article simple retuns the field with the highest ascii value whereas I want the article with the highest number of pageviews.
I couldnt even get the other approach to work.
Stephen, which part of the solution i gave did you have trouble with?
Hi,
I found this solved my issue most simply:
A grouped index where you filter on the index=1
https://www.youtube.com/watch?v=-3KFZaYImEY
Thanks for your time.
Stephen
Briefly:
Create a duplicate of your query.
On one query, choose 'group by' Asset Id, select 'Max' and views. This will give you a 2 column table.
Merge that query with the original, on both columns, choose 'inner join'.
Tidy up the result
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |