Modeling with 3 fact tables

[ayana2]

Hey, I have a modeling challenge that I hope someone can help me resolve. I'm working with 3 fact tables and I need to create a table that draws columns from all the three fact tables. Basically, I'm working with all the columns in the table to answer a variety of questions such as: How productive was the employee this week, this month, this year? Productivitity is based on hours worked (timecard table) and contacts made and actions taken (Contact Report & Action Report). What was the percentage of emails sent by each employee in relation to other actions). I have tries merging some of the tables but that has not offered me a solution. I have created realtionships between the employee table and the 3 fact tables (on Emp ID). I also have a date table, not included in the attached sample file), which connects to the three fact tables (on Date). I have tried using bridge tables but I guess I'm not doing it correctly. I have included a link to a sample file
https://www.dropbox.com/scl/fi/vg87l95httjmk7qqb85d2/SampleDataFile.pbix?rlkey=ck9qy78e54jxotwdti61m... 

I have attached a sample pbix file. 

[ayana2]

Thanks @tharunkumarRTK   I have relate employee and date dimension tables with with three fact tables already. Performing the aggregations is the issue without proper relationships among the fact tables. That's what I hoped to get help with. I have watched a couple of videos including the one you shared and the solution seems straightfoward but when I create the bridge tables, I'm still unable to aggregare across the different tables. I suspect that I'm not defining the bridge tables correctly and I would appreciate some input based on the sample I shared. Thanks!

[tharunkumarRTK]

@ayana2 

As you might already know, Power BI best suits for start schema and relating two fact tables deveiates your modelling from start schema. This will make your dax measures complicated and can cause performance issues. I went through the pbix and could you share the expected result? 

I will try to build the same and can share it with you

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[ayana2]

For some reason my replies are not coming through. I hope this gets posted. Here is a link for sample table that I would like to create: https://docs.google.com/spreadsheets/d/1MAv9ibPjwUARPnNhwXAj4BgCs7wbABMJsynSve50Ooo/edit?usp=sharing 

[ayana2]

For some reason It's not allowing me to add a table. See if you can access this sample file on Google drive: https://docs.google.com/spreadsheets/d/1MAv9ibPjwUARPnNhwXAj4BgCs7wbABMJsynSve50Ooo/edit?usp=sharing 

[ayana2]

For some reason It's not allowing me to add a table. See if you can access this sample file on Google drive: https://docs.google.com/spreadsheets/d/1MAv9ibPjwUARPnNhwXAj4BgCs7wbABMJsynSve50Ooo/edit?usp=sharing 

[tharunkumarRTK · Accepted solution]

@ayana2 

As you can see I added few columns that you asked for, and these coulumns cover all three fact tables. Its just that you need to perform the aggregation appropriately

I am atttaching the pbix for your reference 

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[ayana2]

Thank you! I do see you have the kind of a table I want but then I don't see the solution. I guess what I'm looking for is how to get where you got. For instance, how did you filter the worked hours from a many-to-many relationship so that they can display correctly? 

[tharunkumarRTK]

@ayana2 

As long as you do the modelling properly, having three fact tables wont be a big challenge. Relate employee and date dimension tables with with three fact tables. Perform the aggregations on fact table properly. You can checkout this video for more info.

https://www.youtube.com/watch?v=TnyRsO4NJPc

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[ayana2]

Thank you @tharunkumarRTK . I have related employee and date dimension tables with with three fact tables already. The problem is relating the three fact tables-so I'm having issues with aggregations when the relationships between the fact tables are not well defined. That's the issue I was hoping to get help with. I have watched a couple of videos including the one you shared and while it seems rather straightforward,  when I create the bridge tables, I still cannot fetch some columns. I believe the issue is inability to define the bridge tables properly. And that's what I need help with.