Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello Power Bi team,
I'm trying to list only top 5 records in my visualization, Below are the list of columns/measure used in my scenarios:
Tried with RANK, based on an Account Name column (in Text format) with another measure based on the count of Number (column name = Number) which is also used for ordering. Issue is that Im getting ties and need help in restricting the result set to display only top 5 records.
Scenario 1:
Account Name - in Text format
Ticket Number - in Text format
Quarter year - Need to use filter to show only those records for last quarter (have Column value as "2022_Q4"
Desired result:
Accounts | Year_quarter | RANK |
Account 1 | 3 | 1 |
Account 2 | 2 | 2 |
Account 3 | 2 | 3 |
Account 4 | 2 | 4 |
Account 5 | 2 | 5 |
Account 6 | 2 | 6 |
Account 7 | 2 | 7 |
Account 8 | 1 | 8 |
Account 9 | 1 | 9 |
Account 10 | 1 | 10 |
Account 11 | 2 | 11 |
Scenario 2:
Account Name - in Text format
Ticket Number - in Text format
Quarter year - Records for last 5 quarter
Desired output:
Accounts | 2021_Q1 | 2021_Q2 | 2021_Q3 | 2021_Q4 | 2022_Q1 | Total | RANK |
Account 11 | 12 | 20 | 19 | 12 | 2 | 65 | 1 |
Account 7 | 2 | 7 | 1 | 10 | 12 | 32 | 2 |
Account 8 | 6 | 8 | 0 | 10 | 8 | 32 | 3 |
Account 9 | 7 | 9 | 0 | 1 | 15 | 32 | 4 |
Account 3 | 2 | 2 | 15 | 1 | 9 | 29 | 5 |
Account 6 | 1 | 6 | 8 | 3 | 10 | 28 | 6 |
Account 2 | 2 | 8 | 10 | 1 | 5 | 26 | 7 |
Account 10 | 9 | 1 | 3 | 1 | 12 | 26 | 8 |
Account 1 | 5 | 3 | 9 | 1 | 5 | 23 | 9 |
Account 5 | 2 | 3 | 6 | 3 | 7 | 21 | 10 |
Account 4 | 3 | 3 | 2 | 2 | 8 | 18 | 11 |
Thanks,
Rohith
Solved! Go to Solution.
I was able to find a workaround to my issue.
Below are the steps taken to get the results, there could be other better solution. This is what I have per the power query method
1. Created a unique column by concatenating Accounts and Number column, this is what it was missing from the beginning, It is a TEXT column
2. Created a Ranked column based on the new concatenated column
3. Created a rank for my data requirement
Created a new column -
Accounts | Number | AccountsNumber | _NamedRank | 2021_Q1 | RANK |
Account 11 | A12 | Account 11A12 | 5 | 12 | 1 |
Account 7 | A13 | Account 7A13 | 4 | 2 | 2 |
Account 8 | A14 | Account 8A14 | 3 | 6 | 3 |
Account 9 | A15 | Account 9A15 | 2 | 7 | 4 |
Account 3 | A16 | Account 3A16 | 1 | 2 | 5 |
Thanks,
Rohith
I was able to find a workaround to my issue.
Below are the steps taken to get the results, there could be other better solution. This is what I have per the power query method
1. Created a unique column by concatenating Accounts and Number column, this is what it was missing from the beginning, It is a TEXT column
2. Created a Ranked column based on the new concatenated column
3. Created a rank for my data requirement
Created a new column -
Accounts | Number | AccountsNumber | _NamedRank | 2021_Q1 | RANK |
Account 11 | A12 | Account 11A12 | 5 | 12 | 1 |
Account 7 | A13 | Account 7A13 | 4 | 2 | 2 |
Account 8 | A14 | Account 8A14 | 3 | 6 | 3 |
Account 9 | A15 | Account 9A15 | 2 | 7 | 4 |
Account 3 | A16 | Account 3A16 | 1 | 2 | 5 |
Thanks,
Rohith
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |