cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Krogne Frequent Visitor
Frequent Visitor

RLS removes authorized rows from resulting dataset (truncates)

Hello,

 

We have observed a strange behavior that causes data to be truncated in the visualisations. When viewed as a role with filter on a table with a one-to-many relation to a different table, and the table has invalid keys, the rows are removed completely.

Scenario:

 

Every table that should have RLS, has a column with account ids.

The actual key for the table is a combined key from the account id, and what would be the key without RLS.

the tables has the following relations

Relations

 

When a role with no filter is applied this is the result:

 

When a role that filters on table[accountId] = "asdf" is enabled, this is the result:

As expected.

 

Now a role with filter table[accountId] = "qwer" enabled:

 

Data rows from the sales table are missing. 

 

 

Is this expected behaviour in such a scenario?

 

Are there ways to make it behave as expected when the data is arranged this way? (Besides adding "void" rows to every single table)

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: RLS removes authorized rows from resulting dataset (truncates)

@Krogne,

Create relationship between sales table and supervisor table as shown in the following screenshots. Create same role as described in attached PBIX file.

1.PNG2.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Moderator v-yuezhe-msft
Moderator

Re: RLS removes authorized rows from resulting dataset (truncates)

@Krogne,

What fields of there tables (Employee Table, Supervsor Table, Sales Table) do you use to create relationship?

If you use key field of Employee Table and EmployeeKey field of Sales Table to create one to many rleationship, I note that the Employee Table doesn't contain qwer value. Same scenario occurs in Supervisor table if you use key field of Supervisor and SupervisorKey of Sales Table to create relationship.

In addition, do you create the role filter in Employee table? Could you please copy the data of the above tables in Excel file and share me the Excel file so that I can copy and paste data.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Krogne Frequent Visitor
Frequent Visitor

Re: RLS removes authorized rows from resulting dataset (truncates)

@v-yuezhe-msft

The relations are between:

Sales[EmployeeKey] <> Employee[key]

Sales[SupervisorKey] <> Supervisor[key].

 

Excel: Excel data

 

Role filter is applied on both Employee and Supervisor tables.

 

-Krogne

Moderator v-yuezhe-msft
Moderator

Re: RLS removes authorized rows from resulting dataset (truncates)

@Krogne,

Create relationship between sales table and supervisor table as shown in the following screenshots. Create same role as described in attached PBIX file.

1.PNG2.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 137 members 1,581 guests
Please welcome our newest community members: