Forum Discussion
Virtual Connection Between Two Fact Tables
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.
Can you provide a depiction of the expected outcome please?
Can you provide a depiction of the expected outcome please?
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