Forum Discussion
Report Builder - Ranking 2 most common entries in column
Hi everyone,
This issue is making me insane and I can't figure it out. My 'Dataset5' contains a column with codes in it that are text and numbers like below:
Names
| John5 |
| Alice1 |
| Robert2 |
| Alice1 |
| Michael4 |
| Sarah6 |
| Daniel7 |
| Olivia8 |
| Matthew9 |
| John5 |
What I am trying to do in power bi report builder, is to write an expression that will rank these names according to the two most common. In this case, John5 and Alice1 are the two most common. I want the expression to return the result John5 and Alice1.
Ideally, I would like it to compare against my date column and only rank the two from the previous month.
Can someone help me with this??
1 Reply
If possible, doing the sort order in the query is most efficient. Or having the query return a computed number of occurances.
If you need to do that in the report, you might try either adding a group on name then showing the count in the group details. Or using something like LookupSet() -- https://learn.microsoft.com/en-us/sql/reporting-services/report-design/report-builder-functions-lookupset-function?view=sql-server-ver16
