Create RLS relationship for 2 columns in fact table

[jaryszek]

Hi Guys,

in fact table I have Cost data and want to restrict them based on BillingProfileId and BillingAccountId.

I created Permission Table with User, Email, BillingAccountId, BillingProfileId and trying to connect them to fact tables:

There is the issue that one relationship is inactive.
How to solve it? What is the best practice?

Create 2 roles for both BillingAccount and BillingProfile? 
1 User can have multiple Billing Accounts and Billing Profiles like you can see in my PermissionTable (many to many). 

Please help,
Best,
Jacek

[rajendraongole1]

Hi @jaryszek  -You're encountering an issue where one of the relationships between your PermissionTable and Fact Table is inactive. Since one user can have multiple BillingAccountId and BillingProfileId, and both fields are in your Fact Table, Power BI struggles with activating both relationships at once.

Suggest some best practices:

Since many-to-many relationships can be tricky in Power BI, the best practice is to normalize your model by using a Bridge Table that contains unique combinations of BillingAccountId and BillingProfileId.

Create a distinct list of BillingAccountId and BillingProfileId from the PermissionTable.
Create a Bridge Table from this list.
Link:
Bridge Table → Fact Table (on BillingAccountId & BillingProfileId).
Permission Table → Bridge Table (on BillingAccountId & BillingProfileId).

If your goal is restricting access to data per user, you should use Dynamic RLS.

[Email] = USERPRINCIPALNAME()

enable RLS. 

If you must keep the current model, you can activate the inactive relationship in your calculations.

Total Cost =
CALCULATE(
SUM(FactTable[Cost]),
USERELATIONSHIP(PermissionTable[BillingProfileId], FactTable[BillingProfileId])
)

[jaryszek]

Thank you. 

 tried to implement your suggestions but still there is the issue with many to many relationships:

so I created a new column as new composite primary key BillingAccount-BillingProfile:

It is a good practice? 

On which relationships should i create RLS filters?

PermissionTable --> many to one -->Bridge Table:

BridgeTable --> 1 to many --> Fact Table 

?

Thank you for your help,
Best,
Jacek

[Anonymous · Accepted solution]

Hi, @jaryszek 

Yes. Creating a new composite primary key like BillingAccount-BillingProfile can be a good practice to handle many-to-many relationships in Power BI. This approach helps in uniquely identifying each record and can simplify the relationships between tables.

For Row-Level Security (RLS) filters, it's generally more efficient to enforce them on dimension tables rather than fact tables. In your case, you should create the RLS filters on the PermissionTable and ensure that these filters propagate through the relationships to the Bridge Table and then to the Fact Table1.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.