Greetings,
Problem: RLS works on desktop, not when published
Details: Paginated report shows the specific details for the user logged in based on a user table and defined role. When published, the report shows all the records even when assuming the role of other people.
Data: Excel workbook in Sharepoint. Sharepoint link used as data connection.
Summary: Basically, I created a user table that contains the user name and their email. The dataset has that user name and all of the purchases that person had made. Then I created a role in Power BI desktop and filtered the dataset using this - [Buyer] = USERPRINCIPALNAME(). When I view the report it shows only my records. When I "View As Role" and select another buyer, I only see their records. Then I published and selected the dataset > security option. It showed me the Buyer role and I added all of the buyers to the role. However, when I viewed the report I was able to see all the records. I also tested as another user and still saw all the records. I shared the report with another buyer and they were also able to see all the records. Yet, when I tried to subscribe someone to the report, it said I could not subscribe others because RLS was defined on the datasource.
The only thing that I noticed is that the MS site says the following:
"You can define RLS only on the datasets created with Power BI Desktop. If you want to enable RLS for datasets created with Excel, you must convert your files into Power BI Desktop (PBIX) files first." (https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls)
Question: Do I need to convert my excel files to .pbix, and if so, is there any easy way to import those into Power BI without having to rewire all of my relationships and DAX, etc?
