RLS not working when connecting excel desktop app to a PBI dataset

[GimmeDaData]

Hello,

I have a dataset created with Power BI desktop along with a Report, with an RLS implemented. They are published to a PBI Service workspace.

The RLS works just fine when the data is consumed through that report, however it does not work when using desktop version of excel. For example, when I go to Data -> Get Data -> From Power Platform -> From Power BI (my_company) and then I choose the dataset, it connects to it but the RLS doesn't work, I can see all the data in a pivot table.

What is also weird, is that when I use the "Analyze in Excel" option, a WEB version of excel opens up and in the web browser the RLS DOES work. But when within that file I click to "Open in Desktop App", desktop version launches with the RLS NOT working. What is more, once that desktop version is opened, the web version is being updated and the RLS there stops working / is being overridden somehow.

Please let me know what other information would be useful. 

Thanks,

A.

[GimmeDaData]

Hey, thanks for asking.

There is an excel file containing a list of users with their email and regions they have access to (so multiple rows for a user). We filter that table with the following role rule:

[Email- (Short Form)] = Userprincipalname()

The table sits on tob of others, the place in the model and relationships look correct to me, especially that it works in PBI service.

[lukiz84 · Accepted solution]

Ok, 2 things

- It's very strange that you can see ALL of the data, when connecting via excel (I would assume you see NONE) - why?

- I think, Excel sends domain\username when connecting from it. That's why it's strange that you can see ALL data.

For debugging purposes create a measure which just says

TestMeasure = USERPRINCIPALNAME()

and try this measure in Powerbi, Excel, Excel for Web - would be interesting which results it produces

[GimmeDaData]

This helped!

I use 2 accounts at work, one with bigger permissions than the other.

I logged out of the Office365 with the bigger one, so I thought I was good in regards to that. By using your advice, it turned out that when I downloaded the file to Excel Desktop, it saw my user as the one with bigger permissions, which caused all the panick 😉

I haven't looked into this account mixup yet but the good news is that RLS was working fine all the time.

Thank you!

[lukiz84]

perfect, glad it's working now! have a nice day 🙂

[lukiz84]

How does your RLS look like? What is your ID? (Principal, User, e-mail?)