Row Level Security With Gateway Direct Query

[Arspantejers]

Hi all!

Currently I am developing a dashboard for a small company. 

One of the main requirements is that RLS is applied to only show data specific to that person (so he/she will not be able to see the data of a collegue). I managed this with roles and RLS when using an Excel file as data input. The Excel table used has a column with the name of the employee. 

However, we just switched to using data from a PBI Gateway that has been set up by a third party database administrator. As a result, RLS can no longer be applied on the table that comes from this Gateway. When opening role settings in PBI Desktop it just shows the 'date dimension' table I created manually, not the table with the actual data.

Is there any way to work around this? I could imagine copying the table would work but then data is not really secured...

Thanks in advance 🙂 

[Anonymous]

Hi @Arspantejers Move the excel sheet to either SharePoint or OneDrive and remove the requirement of a gateway.

[AbbasG]

@Arspantejers Hi, can you share some sample data (after removing sensitive information)? It will be much easier to help you faster then.

[Arspantejers]

What might help is the following:

I have a 'Sales' table with columns

| Customer | Product | Amount_sold | Price | Sales_person |

        a              4                 23               3                   X

        a              3                 2                 6                   Z

        b              1                 54               8                   X

        c              9                 1                 7                   Y

Now if person X views the dashboard, he/she should only see the rows where Sales_person = "X"

This is easy to achieve with RLS when I have another data source. With the gateway, however, I am unable to set any roles on my 'Sales' table

Hope this clarifies

[Tutu_in_YYC]

Hello,
having a data source in Power BI Gateway, does not mean you are limited to Direct Query. Can you try switching it to Import mode?

[Arspantejers]

Hey! Thanks for the reply. 

In my PBI desktop I do not have the option:

[Tutu_in_YYC]

At the bottom right of your Power BI Desktop, does it say what mode you are in?

[Arspantejers]

For me it's: "Storage Mode: Mixed"

[Tutu_in_YYC]

Thanks, that means that some of the tables are in direct query mode and some are import mode.

Usually you will be able change the mode from direct query to import for the table. I cannot figure out why you cant do that.

Another option is to import another table that has the information for the RLS and create the role based on that table. ( make sure this new table has relationship with the main table)

[AbbasG]

@Arspantejers Hi, Let's wait for someone from the community to answer this.

[Arspantejers]

Hello AbbasG, 

Thanks for your reply. Sadly I am unable to add any sample data as the entire dataset of the dashboard comes through the Power BI Gateway, and hence, is sensitive..