Many to Many Relationship in Power BI

17.08.2018 / #Power BI


Since the last update of Power BI Desktop (July 2018) you can create many to many relationships (N:M). At the further projects with tabular or Power BI I missed every day the N:M relationships. But since the last project, I hae arranged me with this pain point – and now? I must me rearrange

Okay, what does the N:M compatibilities affect in my projects? In the main points I used bridge tables between those N:M tables.

Following scenario: I have two tables, one with the sales facts, and one table with the users and business units (BU). I need a relationship between these two table by the BU column. A fact has one BU, and a user can be having several BU’s. In this case a 1:n relationship doesn’t works. Further I need a Row Level Security (RLS) by the connected user, so only the user can see his own data.

Before I have N:M, I must create a bridge table with the distinct list of BU’s. In this case I use a M-Script to create this table:

Before I have N:M, I must create a bridge table with the distinct list of BU’s. In this case I use a M-Script to create this table:

Voilà – the expected result:

And now with the new possibility – N:M. Same scenario, same needs.

I can use this both tables and can connect directly to each other – Power BI recognize that a N:M relationship must be used.

I can use this both tables and can connect directly to each other – Power BI recognize that a N:M relationship must be used.

In this case there is no need for a bridge table that must be created with M or other data modelling or ETL tasks. I can also create a RLS on this model and it works fine. I’ve also checked with DAX Studio (http://daxstudio.org/) the query performance, and the query execution time are on the average the same.

But one thing: The possibility is not on SSAS Tabular available. If you have only a Power BI Project without cubes, I think this can helps to improve the build performance and decrease the complexity.

If we can help you or you know how to use our competencies - let us meet for a coffee. You can also call us, send an email, traditional letter or a pigeon.


Related posts


Join the discussion