Forum Discussion
ranking issue
I have sales data for employees In the data we have two levels of employees , high level and low level across mutiple departments .
i want to find rank of employess by the sales they have done
1. Rank of employee across firm
2. Rank of employee in department
3. When the user with level as Highlevel is in scope he should see , rank of his group compared to other groups , when lowlevel employee is in scope we should not show him the group rank it has to show blank.
We have configured RLS on this report in such a way that if a lowlevel user logs in he will see only his data , when a highlevel user logs in he will his his data and all his lowlevel employees of his department .
The ranking criteria i mentioned above should not get effected by RLS .. i assume we have create ranking in columns rather than mesaures for this scenario . Please help
Below are the test data tables
| High level | Low level | Dept | ID |
| highlevel | 1 | 1 | |
| lowlevel | 1 | 2 | |
| lowlevel | 1 | 3 | |
| lowlevel | 1 | 4 | |
| lowlevel | 2 | 5 | |
| lowlevel | 2 | 6 | |
| highlevel | 2 | 7 | |
| highlevel | 2 | 8 | |
| highlevel | 3 | 9 | |
| lowlevel | 3 | 10 |
| ID | Sales |
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
| 4 | 40 |
| 5 | 50 |
| 6 | 60 |
| 7 | 70 |
| 8 | 80 |
| 9 | 90 |
| 10 | 10 |
| ID | Sales | Rank indivual | Rank in Department | Dept Rank in Firm |
| 1 | 10 | 1 | 4 | 3 |
| 2 | 20 | 2 | 3 | |
| 3 | 30 | 3 | 2 | |
| 4 | 40 | 4 | 1 | |
| 5 | 50 | 5 | 4 | |
| 6 | 60 | 6 | 3 | |
| 7 | 70 | 7 | 2 | 1 |
| 8 | 80 | 8 | 1 | 1 |
| 9 | 90 | 9 | 1 | 2 |
| 10 | 10 | 10 | 2 |
@mgwena @cham @amitchandak @Greg_Deckler @Mariusz @yij @yij @ v-yingj@v-diye-msft@v-xicai v-eachen-msft @v-lionel-msft @v-juanli-msft @v-jayw-msf @v-kelly-msft @v-zhenbw-msft
2 Replies
hi Truelearner
For RLS, it works on datamodel level, that means it will filter data in data model, then you use data model to create a measure/column.
In this way, the row context and filter context won't calculate with filtered data.
For example:
If RLS filter data by employee id, now other id will be removed, so it rank will always is "1".
So your requirement will not be achieved in power bi for now.
Hope this could help you.
Regards,
Lin
how abt creating summary data for rank of all criteria and use it in data model that way we have ranks already cacuclated ?
