Relationship between two fact tables and one dimension table - do I need another dimension?

[mashton88]

Hi there,

I have one date dimension table, and several different fact tables. Every fact table is linked to the date dimension table using a date column, which works well to build one 'master table' as per screenshot #2 where every metric/table refreshes when i change the date period for the one table.

However as per screenshot 3, i would now like to build an employee leaderboard, which displays data from two different fact tables. Both these fact tables have a common column to build a relationship with as per screenshot #4, however I cannot make this relationship active as this will conflict with the existing relationship the two fact tables have with the date dimension table.

The relationships that each fact table has with the date dimension table is:

cardinality: Many to one (fact table to dimension)

Cross filter direction: Single

How do I setup the relationships to achieve this? Do I need to build an employee dimension table? Ideally I would like to use both dates and employees as filters to change data from several fact tables.

Many thanks

screenshot1234

[v-easonf-msft]

Hi,  @mashton88 

Could you please tell me whether your problem has been solved?
If it is,  please mark the helpful replies or add your reply as Answered to close this thread.

Best Regards,
Community Support Team _ Eason

[v-easonf-msft]

Hi , @mashton88 

 You can take a try to   use the function of "userelationship" to force PBI to use the inactive relationship.

Make sure you have at least  one  active relationship and one  inactive relationship  set up.

You can refer some similar post and article to learn the use of "userelationship" :

https://community.powerbi.com/t5/Desktop/Userelationship-and-Measures/m-p/282305

https://community.powerbi.com/t5/Desktop/calculated-column-using-userelationship/td-p/206581

https://community.powerbi.com/t5/Desktop/USERELATIONSHIP/m-p/643643

https://docs.microsoft.com/en-us/dax/userelationship-function-dax

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@mashton88  Yes, you need to build an employee dimension table and use both dates and employee tables as filters to change data from several fact tables.

Did I answer your question? If Yes, Give me a Kudo