Dynamic RLS and LOOKUPVALUE vs Many to Many Join

[user131313]

Hi,

I have a data model where I am using dynamic RLS. This means I have a table of users, let's called it Dimension.Users, with a column (let's call it "Fruit") that joins to the main Fact.SalesTable, but by design this is a many to many relationship, because multiple users can have the same Fruit value, and there are multiple sales related to the Fruit, and those individual sales cannot be summarised as that is the data being surfaced. I have one role set up using dynamic RLS and USERPRINCIPALNAME().

I've spent a while investigating this and it seems like the two options are:

- Created a bridging table (just with all possible fruit), and use the DAX function LOOKUPVALUE to pull in the userprincipal name from my Dimension.Users table and then the bridging table filters the Fact.SalesTable as per this video: 

Useful link on getting around dynamic RLS many to many joins

- Just accept the Many to Many relationship and ensure the filter is going from my Dimension.User table over into the Fact.SalesTable

Both of these I've tested and both appear to work completely fine in the service and pbix, but I've read that both of these are not great methods in Power BI (e.g. Many to Many relationships are problematic, as is the LOOKUPVALUE DAX function). Is there a better way? It seems like such a common scenario that there must be!

EDITED ADDITION ON REFLECTION: One benefit I found with the LOOKUPVALUE() approach is that I do need a role within the dynamic RLS that allows access to all rows. So far when trying to work around this in the Many-to-Many approach, I've only found a solution that either blows up my Dimension.User table (to give every option of Fruit back into the Fact for those users), or it uses LOOKUPVALUE() anyway, so I've not gotten around this.

If not - is one better than the other or should I just test relative performance and choose the most efficient? 

Thank you in advance 😁

[v-kathullac]

Hi @user131313 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithanya.

[user131313]

Hi, whilst I'm grateful for the response received. I was looking for some more clear guidance/decision making on which option to follow and which is recommended? But it is possible that there's no answer to this? I did find a resolution to using Many-to-Many in my scenario without LOOKUPVALUE() so my plan is to test out both approaches on a big data model and see which is faster.

[v-kathullac · Accepted solution]

Hi @user131313 ,

Thank you for reaching out to Microsoft Fabric Community Forum.

Below are  key points to help guide your decision :

1)LOOKUPVALUE() can slow down on large fact tables, especially in calculated columns.
2)LOOKUPVALUE Can cause performance issues on large datasets (especially in calculated columns)
3)Many to Many relationships can be faster, but only if the bridge table and related logic are clean and non-redundant.
4)In large models, prefer using measures instead of calculated columns, and test using Performance Analyzer.
5)Since you’ve already found a M:M solution that avoids LOOKUPVALUE():
6)Go with the Many to Many approach if It aligns with your data model structure.
7)Use LOOKUPVALUE() if the relationships aren’t easily modelled.

8)test both approaches using DAX Studio or the Performance Analyzer in Power BI to see what works best in your scenario.

Regards,

Chaithanya.

[Deku]

Many-many tends to be fine if the cardinality isn't massive

[user131313]

Thank you for your response! If the cardinality was massive (I do have a scenario where this would be the case), is it that the Many-to-Many join would create a performance issue?

[Deku]

It is possible but don't think lookup would be better. Would have to test performance. As always it depends