Virtual Connection Between Two Fact Tables

[Anonymous]

Hi!

My model will not let me do a physical relationship between two fact tables and I need thus to do a virtual connection. 

I am working with sales data and need to understand what order number (fact_sales) that is connected to my stock (fact_inventory). I need to to this through connecting my item_keys in each of the tables. Please, see below:

I have added some sample data as you find through this link (both excel & power-bi files): https://docs.google.com/spreadsheets/d/11adB3fwihvfHsBlWP40HHghzZj0QUSYm/edit?usp=sharing&ouid=11610...

Very happy if someone can help me out. Let me know if any questions.

Big thanks!

[McDermoW5 · Accepted solution]

Hi,

I'd actually advise you use the Fact Sales table as a central table. This makes the model simpler. First go to Home / Modelling (depending on what view you're looking at) > Manage relationships. This shows all the relationships in your model as a list so just de-select the relationship between 'Fact_Inventory' and 'Dimension_Item'. Then click New..., select the tables from the drop downs, click on the [item_key] columns, ensure the "Make this relationship active" box is ticked then press "Okay".

If you need to use something closer to the model you have now, you could also do the same but have the relationship between Fact_Inventory and Fact_Sales as inactive and use the USERELATIONSHIP() function here on columns or measures that need it.

[PaulDBrown · Accepted solution]

Can you provide a depiction of the expected outcome please?

[PaulDBrown]

Even though creating a many-to-many relationship between two tables may seem a way to solve this, best practices establish that these type of relationships should be avoided, since you might encounter strange results in measures and create ambiguity.

You can see the " pernicious"effects of these n:n relationships in the following video:

https://youtu.be/NdrrjkvH2zo 
The way you can solve this particular issue is to write measures either using TREATAS or CROSSFILTER, keeping the model as you had it originally (which is how it should be based on best practices)

If you provide sample data, we can help you with the measures.

You can see an example of how to solve a similar problem in this thread:

https://community.powerbi.com/t5/Desktop/Many-to-Many-relationship/td-p/2124907 

[McDermoW5 · Accepted solution]

Hi,

I'd actually advise you use the Fact Sales table as a central table. This makes the model simpler. First go to Home / Modelling (depending on what view you're looking at) > Manage relationships. This shows all the relationships in your model as a list so just de-select the relationship between 'Fact_Inventory' and 'Dimension_Item'. Then click New..., select the tables from the drop downs, click on the [item_key] columns, ensure the "Make this relationship active" box is ticked then press "Okay".

If you need to use something closer to the model you have now, you could also do the same but have the relationship between Fact_Inventory and Fact_Sales as inactive and use the USERELATIONSHIP() function here on columns or measures that need it.