data filtering on user login

[Anonymous]

I have a table where list of users are present with facility name next to them,

each email/user can have multiple facility with them
User data table :

Email	Facility
xyz@abc.com	A1
xyz@abc.com	A2
xyz@abc.com	A3
hello@abc.com	A1

Data table:

Data	Facility
1111	A1
2222	A2
3333	A3
4444	A1
5555	F1
6666	C1
7777	D1
8888	Y1
9999	Z4

when xyz@abc.com views the report he should be able to view only data with A1 ,A2, A3 in second table.

similarly hello@abc.com should see the records in data table only with A1 facility data


is this use case achievable ?

[v-deddai1-msft]

Hi @Anonymous ,

You can use dynamic rls :https://radacad.com/dynamic-row-level-security-with-power-bi-made-simple

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[pranit828]

Hi @Anonymous 

I would use the USERNAME() function to get the logged in username and create a table with single row and column using this function

Now I will trim both output of USERNAME() data and [Email] in data User data to create a connection.

After Connection is created Only data for the logged in user will be visible.

[amitchandak · Accepted solution]

@Anonymous , This is case for RLS. But you need this relation in a table like A1 can see A1, A2, A3

RLS - Row Level security
https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-Unleash-row-level-security-patterns-in-Power/td-p/712613
https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi
https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies
https://docs.microsoft.com/en-us/power-bi/service-admin-rls
https://blog.tallan.com/2018/04/10/row-level-security-in-power-bi-part-1-roles-and-users/
https://radacad.com/what-do-you-need-to-implement-dynamic-row-level-security-in-power-bi

[Anonymous]

@amitchandak 

Can you briefly explain how to do the relation and achive this?

[v-deddai1-msft]

Hi @Anonymous ,

Just create many to many relationship between two table and add role [Email] = userprincipalname() in user table:

Then publish it to service. Assign users to Power BI Security:

Last share the report with the user, he will see the data belongs to him:

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

You need to use RLS, stright forward to set, take a look at the following links. 

https://www.youtube.com/watch?v=MxU_FYSSnYU

https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls