Per User RLS

[olimilo]

So I did post a question about this before, read up on the answer but that didn't seem to answer my question as it had a Group-level RLS. What I aim to do is have a User-level RLS that doesn't depend on a group. It works the same way that one person can only see their own training profile and not others, so as you can see, there are no groupings involved. If I were to do the solution on the attached link, that would mean that I have to create a separate role for each user which doesn't seem to be very intuitive.

I had tried using USERNAME() in a column but I end up getting an error. The way I understood it is that it should filter the TrainingInformation shown for the currently logged in user (eg: global\12345)? Can anyone suggest how this can be achieved?

This is my current data structure:

[vanessafvg]

@olimilo

does this help?

http://radacad.com/dynamic-row-level-security-with-power-bi-made-simple

[olimilo]

Hi @vanessafvg

I'm not really sure what I'm doing wrong here. I modified the data structure to keep it streamlined:

Auditor List

1. Domain ID = the Domain username of the auditor

2. Name = full name

Training Information

1. NameDisplay = full name (FK)

2. TrainingTypeCategory = training category

3. TrainingType = training type

4. TrainingCode = code (FK)

Training Codes

1. Code = training code (PK)

Questions:

1. Where should the USERNAME() measure be made? In the Auditor List or Training Information table? In the link's sample, it just says:

So in Data Tab, create a new measure, and name it User, with value of USERNAME();

So which one is the "data tab"? Is it in the Transactions or the Sales Rep table? Or is it a new table?

2. I made a User = USERNAME() measure under the TrainingInformation table, and made this role:

It still doesn't filter out the data even when I'm viewing as the role. I'm really confused, where should the USERNAME() table filter be in?

[Salvador · Accepted solution]

Hello,

I use for that same purpose USERPRINCIPALNAME(), wich gives, in my case, the user's email address, and I check the user that way, hope it helps 😄

[olimilo]

Thanks @Salvador, I did as suggested and it seemed like a better choice to use USERPRINCIPALNAME() instead of USERNAME() as the latter returned different values when I tested them on the service.