How can I use PowerBI's Row Level Security to implement an employee filter on a website?

Question

I have to embed a PowerBI scorecard visual on a website. I've set up the scorecard in PowerBI Desktop, and it looks fine; the slicer for the scorecard filters the employee name.

I'd like for the employee that visits the company website to see only their data in the scorecard- in other words, their presence on the website would automatically filter the scorecard to reveal their statistics only. Fortunately, they have to login to the company's website by providing their Windows credentials.

I'm not sure how to go about doing this. I understand that I have to use the "Security" portion of the "Modeling" tab, and I've (successfully) created an explicit role for one viewer by using the following code:

'Table1'[Employee Name] = "John Smith"

Of course, this only reflects John Smith's statistics when I click "View as Roles." I don't think it's practical to explicitly write a DAX code snippet for every single employee, since my company has 3000+ employees.

Can anyone share any instructions, or lead me to some?

Thank you in advance.

Answer 1

I had to deal with a similar issue, though we view our dashboards within the Power BI service, so that is the authentication I am familiar with. The basis for this form of dynamic row level security is that the expression written is evaluated for each row in the indicated table to either True or False.

If your user has signed in to the you will be able to determine the identity using USERPRINCIPALNAME(). This is the email address they use to sign into the service.

Since you are displaying data specific to that user, it is possible you already have their email in your data. If that is the case, and it is simply another field in your table your validation line would look something like:

'Table1'[Email] = USERPRINCIPALNAME()

The left side of the expression refers to the row you are referencing, while the right will supply the name of the authenticated user. If you don't have that available in your existing data, you could use a DAX expression to lookup the Employee name from a list of emails and their associated names.

In this case, you only have one role which everyone needs to be assigned to, and the logic above will determine which rows they can see.

This is a simple example in which each employee can only see their own data. The model can be extended by creating 'security tables' with users and their associated records, and then utilizing the CONTAINS() function to check authorization, but it sounds like you have a 1-to-1 relationship with what individuals should be able to see.