Forum Discussion
Multiple fact tables for the same dimensions in tabular
Hi, im making a tabular model to use in Power BI and i have more than one fact table related, (Orders, sales, shippings) and all the tables are related to the Client, Items, Calendar dimensions but I can only use these with one of the fact tables because the tabular model allows me only one active relationship. How can i solve this?
- I am trying not to create multiple sets of dimensions for each table of fact
-everytime that i would like to make reference to a dimension which isnt active use DAX USERELATIONSHIP expression into a measure but what about an item description?
-I also thinking to migrate the project to a multidimensional model but I do not know if I will solve the problem cause I do not have much experience.
I would like to know a better solution from this great community
Thanks! and sorry for my bad English.
7 Replies
Hi, im making a tabular model to use in Power BI and i have more than one fact table related, (Orders, sales, shippings) and all the tables are related to the Client, Items, Calendar dimensions but I can only use these with one of the fact tables because the tabular model allows me only one active relationship. How can i solve this?
- I am trying not to create multiple sets of dimensions for each table of fact
-everytime that i would like to make reference to a dimension which isnt active use DAX USERELATIONSHIP expression into a measure but what about an item description?
-I also thinking to migrate the project to a multidimensional model but I do not know if I will solve the problem cause I do not have much experience.I would like to know a better solution from this great community
Thanks! and sorry for my bad English.
- vanessafvg
Community Champion
Anonymous it will be easier to understand how to help you if you provide more information.
what exactly do you want to do that you are not able to do? can you show me what your relationship view looks like, it sounds to me like maybe something else is going on please screen shot your relationship view
Hello vanessafvg, thanks for your answer, below I attach an image of my tabular model. I can correctly analyze each of the fact tables separately using the dimension tables but I am having problems when using the non-active relationships cause I am not able to relate Shipments with Sales that are linked through Deliveries by two non-active relationships . How can i do this?
Tabular model
Am I using the DAX USERELATIONSHIP function correctly?
Shipment Date = CALCULATE(MAX(Shipments[dt-embarque]);USERELATIONSHIP(Shipments[nr-embarque];Delivery[nr-embarque]))Thanks for you collaboration :)
Hi Praveen,
Fact tables should not be related together : dimension table are related to Fact tables. If you need to relate fact, then you should ask yourself : what is the exact need ? Could I create a new dimension to express the need, and then I will relate this new dimension to multiple fact tables, which is of course always possible (except if data is buggy). Does this help ?
Please look at the Microsoft GuyinACube video on multiple dimensions and multiple fact tables.
