Forum Discussion
Relationship with 2 fact tables
I am trying to create a realtionship rom 2 favt tables so that when a selection is made from the dept (filters the emp slicer) to display on the matrix table
i think you need to combine employee and department? does it make sense to do that? and also add a column in that table with username from fact 2, so you can join fact to to the employee dept table. you wil need the facts to share dimensions in order to filter both, unless you have a bidirectional relationship which i dont recommend, better to denormalise(flatten out) your model.
3 Replies
- vanessafvg
Community Champion
i think you need to combine employee and department? does it make sense to do that? and also add a column in that table with username from fact 2, so you can join fact to to the employee dept table. you wil need the facts to share dimensions in order to filter both, unless you have a bidirectional relationship which i dont recommend, better to denormalise(flatten out) your model.
Hi TechPain , vanessafvg, thank you for your prompt reply!
For your requirement, try as following:- Add the EMP# and Dept# columns from the dim table to FactTable#2:
- Then create the active relationship between FactTable#2 and all dimension tables as shown below:
- Next, create three measures and sum the different types of values in FactTable#2:
Total Book FactTable2 = CALCULATE(SUM(FactTable2[total]),FactTable2[Type]="Book" && FactTable2[EMP#]<>7777)Total Cancel FactTable2 = CALCULATE(SUM(FactTable2[total]),FactTable2[Type]="Cancel"&&FactTable2[EMP#]<>7777)Total Rebook FactTable2 = CALCULATE(SUM(FactTable2[total]),FactTable2[Type]="Rebook"&&FactTable2[EMP#]<>7777)- Lastly, add a Matrix visual and slicers you need to display it:
Result for your reference:
Best regards,
Joyce
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you, but it didnt work for me...
