data filter by username

[rdantas]

Hello! I want to filter date on my report, by the username logged on. In my database, I have the username. So I wanna compare the username logged on with the username in the database. I want the username logged on only see the lines with your email on the database.

user1@mail.com must see only the lines 1 and 2, so on...

How can I do this with RLS?

[Bipin-Lala · Accepted solution]

Hi @rdantas,

What you are trying to implement is called Dynamic RLS in Power BI, which is restricting data for the users, based on their login credentials.

Although I would have tried explaining Dynamic RLS here, but we already have some excellent resources available, which can help you with implementing your requirements. Please refer to the following links - 

• https://radacad.com/dynamic-row-level-security-with-power-bi-made-simple
• https://learn.microsoft.com/en-us/power-bi/enterprise/service-admin-rls
• https://databear.com/dynamic-row-level-security-in-power-bi/

Let me know if the above links help you out. In case they don't, could you please elaborate on your question a bit, I will try to provide a solution here only. All the best!

[lbendlin · Accepted solution]

Read about dynamic RLS and USERPRINCIPALNAME()   (don't use USERNAME() )

[Anonymous · Accepted solution]

Hi @rdantas ,

lbendlin is right on the money. Let me take you through the specifics.

Be aware that username() has the format of DOMAIN\username within Power BI Desktop. Within the Power BI service and Power BI Report Server, it's in the format of the user's User Principal Name (UPN). Alternatively, you can use userprincipalname(), which always returns the user in the format of their user principal name, username@contoso.com.

You cannot assign a user to a role in Power BI Desktop. Assign users in the Power BI service. Dynamic security in Power BI Desktop can be enabled by using the username() or userprincipalname() DAX functions and configuring the correct relationship.

Please follow these steps:

Open the report in Power BI Desktop.

Navigate to the Modeling tab and select Manage roles.

Create a new Role, for example, name it Role1.

Enter the DAX expression on the right side as shown below.

Save the role.

In the Modeling tab, select View as.

Select the Role1 you just created.

I am testing this on Power BI Desktop in a virtual machine, so my userprincipalname is in "domain\username" format, which should not be wrong in Power BI Service.

If you are not sure, you can create a measure:

measure = userprincipalname()

And then drag it to the Card page to display it so you know your current userprincipalname.

Successful validation looks like this:

Once you are satisfied with the settings, publish the report to Power BI Services.

In the Power BI service, you can assign the appropriate users to the created roles.

For a more detailed guide to setting up RLS, see the official documentation here:

Row-level security (RLS) with Power BI - Power BI | Microsoft Learn

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[lbendlin · Accepted solution]

Read about dynamic RLS and USERPRINCIPALNAME()   (don't use USERNAME() )

[Bipin-Lala · Accepted solution]

Hi @rdantas,

What you are trying to implement is called Dynamic RLS in Power BI, which is restricting data for the users, based on their login credentials.

Although I would have tried explaining Dynamic RLS here, but we already have some excellent resources available, which can help you with implementing your requirements. Please refer to the following links - 

• https://radacad.com/dynamic-row-level-security-with-power-bi-made-simple
• https://learn.microsoft.com/en-us/power-bi/enterprise/service-admin-rls
• https://databear.com/dynamic-row-level-security-in-power-bi/

Let me know if the above links help you out. In case they don't, could you please elaborate on your question a bit, I will try to provide a solution here only. All the best!