Filter by value coming from another cell value (also filtered by Username) - RLS and DynamicQuery

[Anonymous]

I am new in Power BI and I faced with some issue I event don't know is possible to solve.

I have the following simple table with Persons.

a) each Person has a its own ID  [PersonId]

b) each Person has one manager identified by [ManagerId]

c) [ManagerId] coresponds to [PersonId]

For instance:  Anna is manager of John and Phil.

PersonId	Username	FirstName	ManagerId
1	j@test.com	John	5
2	p@test.com	Phil	5
3	s@test.com	Sara	6
4	d@test.com	Donald	7
5	a@test.com	Anna	8

Question: Is it possible to filter based on the username in RLS  in such a way:

- User can see data comming from "its" row and also from other user where he is assigned as a manager.

i.e: 

a) John will see only its own data (first row)

b) Anna will see her data (last row) and also John and Phil.

So far I did the simple RLS expression like [Username] = Username() but I don't know to handle this case.

I'm using datasource from MS SQL  in DirectQuery mode !

[TeigeGao · Accepted solution]

Hi @Anonymous ,

In your scenario, we can create a table in SQL Server with CTE, this table will contains the personid and each id it can manage, then import this table to PowerBI to filter data.

Best Regards,

Teige

[Anonymous]

Hi,

I managed to solve my problem thanks to @TeigeGao suggestion  - I used CTE functions and then the result was imported to Power BI (despite I rejected such a solution at the begining because of necessity to extend db ).

In additional I tried to call my CTE function for particular logged user but it seems to be not possible.

USERNAME() or USERPRINCIPALNAME() are  available in DAX expression not in Power Query where I wanted to call my sql function with "user" paramer.

Maybe someone have some hint to do this?

[Anonymous]

Thanks @TeigeGao  for suggestion.

But I'm still wondering if there is any possibility to do that only on Power BI sde. 

In my case the database should not be changed for this (preparing reports) purpose. Let's assumme db was prepared and is maintaining by other team and I need to use only what I have now.

Of course demontrated table was only a shortcut compared the real database 🙂 but the clue of the problem is the same.