RLS table to filter multiple columns in the fact table

[Motwi]

Hello everyone,

I'm having a tough time trying to apply an RLS logic to filter two different things at the same time, please see below.

I have a RLS table, that looks like this:

User	Region_code
john.dow@email.com	rg001
john.dow@email.com	rg002
john.dow@email.com	rg004
jane.doe@email.com	rg001
jane.doe@email.com	rg003

On the modeling I have a RLS Role, where it is applying a filter on this table with the following code:
[user] = userprinciaplname()

This table is connected with another table called dim_Regions(below) between the Code and the Region_code. The connection is bidirectional and I checked the "Apply security ...." option.

Region	Code	Description
UK	rg001	United Kingdom
USA	rg002	United States of America
IT	rg003	Italy
ES	rg004	Spain

This dim_Regions table is connected to the fact_sales table from the Region with the LineManager_Region on the fact_sales. Everything works fine, when John Dow logs in, the RLS table filters the dim_Regions table which, in turn, filters the fact_sales, looking like this:

Invoice	Officer_region	LineManager_Region	Amount
INV001	ES	USA	100
INV002	ES	UK	150
INV003	IT	UK	200
INV004	ES	ES	150

The new requirement complicates this a bit, now I need to filter by the Officer_region as well. It's like dim_Regions needs to have two active relationships, one to the existing LineManager_Region and another to the Officer_region, but that's not possible in Power BI. This is what I need to achieve for John Dow:

Invoice	Officer_region	LineManager_Region	Amount
INV001	ES	USA	100
INV002	ES	UK	150
INV003	IT	UK	200
INV004	ES	ES	150
INV005	UK	IT	300
INV006	USA	IT	350

So you can see, John Dow now sees inv005 and inv006, because the Officer_region is in UK or USA, dispite the fact the that LineManager_Region is in IT, which is a region is would not have access through the RLS.

I hope this makes sense, please let me know if you have any suggestions solving this problem.

Thank you so much in advance

[Motwi]

Thank you very much for your suggestion Gao.

I've created everything like you did, but for some reason it doesn't work on my side.

I have power bi updated to the latest version now, and it still doesn't work, maybe I'm missing something?

However, looking at your demo, it seems that RLS is not being handled completely on your side as well? When I view as Jane Doe, she can see more than we'd expect. Don't know if I'm doing something wrong, though. Please see below.

Thank you so much for your help on this.

Best Regards

[Anonymous]

Hi @Motwi ,

I made some changes to the security roles:

VAR __region = CALCULATETABLE(VALUES('dim_Regions'[Region]), 'RLS table'[User] == USERPRINCIPALNAME())
VAR __filter = 'fact_sales'[Officer_region] IN __region || 'fact_sales'[LineManager_Region] IN __region
RETURN
    __filter

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Motwi]

Hi Gao,

Thank you so much for you help. I can see that it is working on your side, but when I apply the code on my report that uses real data, it doesn't work. It says "The function expects a table expression for argument '', but a string or a numeric expression was used."

If I click save and go check what is in the fact table, the result is very odd.
Translating the results from that report to this example, I would see 3 rows on the fact table. The column names would be from the fact table, but the records on these 3 lines are from the RLS table. It looks like this:

Do you know what is going on?

Again, thank you for your time and pacience with this.

Best regards

[Anonymous · Accepted solution]

Hi @Motwi ,

-- The function expects a table expression for argument
Double-check that CALCULATETABLE is used here instead of CALCULATE. the former returns a table, while the latter returns a scalar.

VAR __region = CALCULATETABLE(VALUES('dim_Regions'[Region]), 'RLS table'[User] == USERPRINCIPALNAME())

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Motwi]

Hi,

It worked, thank you !

Apologies for the late reply, but I wasn't able to revisit this for a while

Best regards

[Anonymous]

Hi @Motwi ,

Consider modeling it this way:

Create such a rule in fact_sales:

Output:

The downside is that dim_Regions filtering cannot be passed directly to fact_sale and may need to be passed indirectly via variables.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum