Table contains foreign key to multiple tables

[IntaBruce]

I have a curious data model from which I need to create Power BI reports.

In this model there is an Assignments table which contains foreign keys to two other tables, enabling a m:m relationship.

One of those foreign keys is consistent in that it always relates to the Assets table and the key is Asset_UID.

The other key is called Record_UID and it relates to one of several tables, the relevant table being determined by Entity_UID.

I've not seen this sort of data structure before, but then I'm not a Data Scientist so it may actually be fairly comon and even have a name?  I didn't create the data model and I have no influence over it, I'm just trying to report on it.

To make sense of things I have used Power Query to create multiple copies of the table, each filtered on Enitiy_UID and then create the relationships in Power BI to the relevant table, dictated by the Enity_UID.

Is this the right thing to to?  Or is there a better/simpler approach I could take?

Example: A row in the table could link an Asset to another Asset, an Asset Type, a Work-order, or an Event, with seperate tables for each of these. 

[lbendlin]

Is it M:M single directional, or bidirectional?  How familiar are you with Qlik's associative model?

[IntaBruce]

I'm not at all familiar with Qlik's associative model I'm afraid, I just Googled it and it looks like something that is proprietary.

As for the relationships, the Assignments table is what enables a m:m relationship between say Asset and Work-order.  A work-order may use zero, one or many Assets and an Asset may be used in zero, one or many Work-orders.  I'm not sure about the direction but I have found that Power BI will not allow me to make them all bi-directional.

[v-priyankata]

Hi @IntaBruce  , Just checking in as we haven’t received an update from you regarding last response. Were you able to resolve the issue with @lbendlin 's answer?

If solution was helpful, please mark it as Accept Answer and click Yes to confirm.

Feel free to let us know if you have any further questions—we’re here to help!

Thank you for being a part of the Microsoft Fabric Community Forum!

[IntaBruce · Accepted solution]

Hi @v-priyankata ,  Whilst @lbendlin's reply was a useful pointer to star schemas, on which I need to do quite a bit more reading, it didn't really answer my question.  However CoPilot did; here's what it said:

It sounds like you're dealing with a polymorphic association, which is a design pattern where a single foreign key in one table can reference multiple other tables. This is not uncommon in data modeling, especially when dealing with complex relationships.

Your approach of using Power Query to create multiple copies of the table, each filtered by Entity_UID, and then creating relationships in Power BI is a valid solution. However, there are a few other approaches you might consider:

1. Union Tables: Instead of creating multiple copies of the table, you could create a union table that combines all the possible related tables (e.g., Assets, Asset Types, Work-orders, Events) into one. This way, you only need one relationship in Power BI. You can add a column to this union table to indicate the type of entity (Asset, Asset Type, etc.).

2. Bridge Table: Create a bridge table that maps Record_UID to the appropriate table and Entity_UID. This bridge table can then be used to create relationships in Power BI. This approach can help simplify the model and make it more scalable.

3. Dynamic Relationships: Use DAX to create dynamic relationships based on the Entity_UID. This can be more complex but allows for more flexibility in your reports.

  I'm going to mark that as the answer I think.

[lbendlin]

Bridge tables are a warning sign for an inefficient data model. They are the opposite of scalable.

[lbendlin]

Exactly, because then it would be Qlik....

Power BI wants 1:* relationships, single direction. Read about star schemas.

[IntaBruce]

Thankls for the pointer on Star schemas, that was useful and will be in future.