Forum Discussion
Relationship between multiple facts
I have 2 fact tables that I need to somehow relate to common dimension. I can't make all the relationships active as it will lead to ambiguity.
Spaces contains the information about my Rooms and and is linked to a Property hierarchy.
Assets contains information about assets in each room. A room can have multiple assets.
Orders contains information on all Work orders. A space can have many work orders. An Asset can belong to many work orders. Not all Orders have an Asset_key.
I want to be able to filter by Spaces but I also want to filter the Assets by the Orders. How can I achieve this in my model?
- Do I create a bridge table of All Orders and All Assets?
- Do I make my existing relationships to Space Bi-directional?
Each Fact has it's own set of corresponding dimensions.
For some measures I have used USERELATIONSHIP() but this doesn't help when filtering tables of data.
I'm sure this must be a common problem but I had difficulty searching for the correct answer.
Thanks
Hi andy_scott42 ,
For the relationship, recommend you create the star schema instead of circle schema, so you may delete the secondary relationship between table Orders and Assets, and change the Cross filter direction of relationships among the these tables above from Single to Both , which will take these tables treated as a single table. Then when you make some changes in someone table, the other table objects will return corresponding matched result. See more:Create and manage relationships in Power BI Desktop .
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
andy_scott42 if you are planning to filter both fact tables from the same slicer (dimension), yes as a best practice, create dimension table that has a relationship with both the fact table. Avoid setting the relationship between facts with many to many or cross filter direction to both, it has performance implications when working with a large datasets or complex dax measures. I hope it helps to get started.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
parry2k thanks.
My issue is the Asset_key in Orders can be null, so it needs to link to Space to get all the Orders.
Otherwise I would use Asset as a dimension for Orders, as that would filter up to Spaces.
andy_scott42 , You need to common dimensions of space and asset. Refer how to deal with Many to Many
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-many-to-many-relationships
https://www.seerinteractive.com/blog/join-many-many-power-bi/
https://radacad.com/many-to-one-or-many-to-many-the-cardinality-of-power-bi-relationship-demystifiedHi andy_scott42 ,
For the relationship, recommend you create the star schema instead of circle schema, so you may delete the secondary relationship between table Orders and Assets, and change the Cross filter direction of relationships among the these tables above from Single to Both , which will take these tables treated as a single table. Then when you make some changes in someone table, the other table objects will return corresponding matched result. See more:Create and manage relationships in Power BI Desktop .
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks Amy v-xicai
That seems to have fixed my issue and enabled the filters to work as expected.
However this has changed the value of my measures so I'm not sure it is the correct solution.
I was under the impression that setting filters to bi-directional was bad practise as it leads to performance errors and incorrect measure values?
Hi andy_scott42 ,
You may need to change the formula of measure according to the fact value.
Best Regards,
Amy
