Power BI - Creating a RLS role based on multiple tables?

[Anonymous]

Hi,

I would like to create a Row Level Security Role based values from columns in multiple tables.

For example, I have created a Role named 'North' against the tbl_DailyE2E_Div table which has the following DAX expression:
[Region] = "Midlands" || [Region] = "North Central" || [Region] = "North Total"
This works fine.
I would like to extend the DAX expression, and so this particular role, to include another table named tbl_DailyE2E_FC, so the full DAX expression will be like:
---------------------------------------------------------------------------------------------------------------------
[Region] = "Midlands" || [Region] = "North Central" || [Region] = "North Total"
&&
[tbl_DailyE2E_FC].[Division] <> "Central" || [tbl_DailyE2E_FC].[Division] <> "Overall"
---------------------------------------------------------------------------------------------------------------------

There is no relationship between the tbl_DailyE2E_Div and the tbl_DailyE2E_FC tables. No relationship has been needed as the page has a visual showing data from tbl_DailyE2E_Div and another visual showing data from tbl_DailyE2E_FC. All I would like to do is, dependant on the user (role), have the user will see data in one visual but not in the other.

I do intend to include further tables in the role but if I can get the DAX expression to work for two different tables then entending the DAX to include more tables shouldn't be a problem.

Thanks.

[TeigeGao]

Hi @Anonymous ,

Generally, we cannot create a filter using && on two tables when creating RLS, as a workaround, we can use calculate function to generate the result, please refer to this case: https://community.powerbi.com/t5/Desktop/RLS-rule-to-filter-on-multiple-tables-with-quot-OR-quot-logic/td-p/165249

Best Regards,

Teige

[Anonymous]

@TeigeGao  thanks for the response.

The project I'm wokring on isn't your standard star schema dimension with a single fact table having a location/geography/etc.. dimension.  It's esstentially many aggregated views that have been provided to place into visuals to show that view's data. I'm mentioning this as it may dictate the solution I need, i.e. with the CALCULATETABLE function there is just one fact table that I am looking to filter down - this isn't the case here.

I'll explain in a different way.
For simplicity reasons I'll shorten my project/requirement.
I have a report that has two pages. One for Full Company and one for North. The Full Company page shows data from the tbl_DailyE2E_FC table.  I have created a RLS Role named 'Full Company' that allows users, assigned to the role, to view filtered data from the tbl_DailyE2E_FC table. See below:

The North page shows data from the tbl_DailyE2E_Div table.  I have created a RLS Role named 'North' that allows users, assigned to the role, to view filtered data from the tbl_DailyE2E_Div table. See below:

When testing the Role via 'View as Roles', within the Power BI Desktop, the Full Company page shows data from the tbl_DailyE2E_FC table when using the Full Company Role.
When using the North Role the North page shows data from the tbl_DailyE2E_Div table - which is good. I do have other pages (East, South, West) that show data from the same tbl_DailyE2E_Div table, but filtered based on a role, and these do not show data when viewing as the North role, the visual are empty - again this is expected and correct!

However, and this is the question I'm asking, when viewing as the North role the Full Comany page still shows all the data from the tbl_DailyE2E_FC table. What I need to happen is that when viewing as the North role the Full Company page's visuals need to be empty. So I expect that when telling the North role to include the Regions from the tbl_DailyE2E_Div table, I also need to tell it NOT to include Divisions from the tbl_DailyE2E_FC table.

Hope this all makes sense!!

The tbl_DailyE2E_FC and the tbl_DailyE2E_Div tables are not related, remember that are completely separate views.

How do I achieve what I am trying?  Please provde example DAX.

Thanks.

[SEMattis]

Hi @TeigeGao  & anonymous,

Did you guys find a solution to the issue described? I'm facing the same issues and woul very much like to know how you solved the issue at hand.

Thanks!

[Anonymous]

Hi @SEMattis  @TeigeGao  did anyone found the solution i am at the exact position now.