Multiple relationships: systemuser in Dynamics - Created By on multiple tables

[AnneSophieD_]

Hi everyone,

I've imported Dynamics data into Power BI via Dataverse. One of the tables is 'systemuser', which contains all users with their names, IDs, etc. Other tables like 'account', 'contact', 'lead', etc., have fields such as 'created by', 'owned by', 'modified by', etc., which reference the 'systemuser' table.

I'm trying to create an adoption dashboard in Power BI to report on system usage. I've added all tables via Dataverse into Power BI, including the 'systemuser' table. However, I added the 'systemuser' table without relationships because adding them creates a 'loop' due to relationships with multiple tables.

How can I resolve this so that I can use a single filter on 'username' that works across visuals from all tables?

Thanks in advance!

[Rupak_bi]

hi @AnneSophieD_ ,

A loop error comes when you connect a dimention table to multiple fact tables while fact tables are also directly connected. that is to avoid. identify those fact table to fact table connectioncolumns and include distinct of those columns in the dimention table. 
to get a better understanding, share sample data of all such tables in your model and current relations( which creating a loop) so that we can guide on how to create a bridge table for specific case.

[Idrissshatila]

Hello @AnneSophieD_ ,

the best approach in building your model, is that the system user table which is a dimension table should be linked to all fact tables. 

[AnneSophieD_]

Yes indeed, but my question is how do i do this?

[Kedar_Pande]

@AnneSophieD_ 

Instead of directly linking the systemuser table to multiple tables (like account, contact, etc.), you can create a bridge table for the user IDs. This avoids relationship loops and allows a single username filter to work across visuals.

UserBridge = DISTINCT(SELECTCOLUMNS(systemuser, "UserID", systemuser[systemuserid], "UserName", systemuser[fullname]))

💌 If this helped, a Kudos 👍 or Solution mark ✔️ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[AnneSophieD_]

Hi Kendar,

Thanks for your reply. Could you explain this more into detail?