ranking issue

[Truelearner]

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

[v-lili6-msft]

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

[Truelearner]

how abt creating summary data for rank of all criteria and use it in data model that way we have ranks already cacuclated ?