Handling MULTIPLE fact tables in Power BI

You've got multiple fact tables in your Power BI dataset. And, dimension tables for each. Struggling to slice and dice across both? Patrick shows you how to deal with this mess.

Understand star schema and the importance for Power BI


*******************

Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.


*******************
LET'S CONNECT!
*******************


***Gear***

#PowerBI #DataModel #GuyInACube

[thesmoothman]

I like how you use the Description property to list tables you can group by! Putting that one in my pocket!

[PaulSlowey]

Guy in a Cube has to be one of the best channels on YT!!!! Everything makes sense, Adam and Patrick have welcoming and down to earth styles, and I finish each video feeling good that I've learnt something new I can apply straight away. And the videography, illustration/animations are clear and comprehensible. Doing my DA-100 exam tonight - this has been the best resource I could have found to help me piece everything together. Thanks do much.

[sarahvullo]

THANK YOU, THANK YOU, THANK YOU! I’m just getting to grips with Power BI, but every time I google an issue I get only Dax related solutions - and I don’t want to go down that path right now. I was thinking the solution to my issue would be complicated, but you made it so clear and easy that I’ve fixed it in 5 minutes - having wasted a day searching for the answer on other forums. I cannot thank you enough. I really love your presentation style too - so fun to listen to and easy to follow.

[barmalini]

I love how correctly you've described a Fact-Constellation pattern without mentioning its name once!
-To all, this guy very well knows what he's talking about!

[odianosenakhibi]

I had been dealing with a broken report for almost two weeks and this literally saved my job!!!! Thank you

[pedropradocarvalho]

Great video, BUT, i think you should record a 1h+ long video about this topic, i know you already have 3+ other videos on modeling. Please, do a huge, complete one :)

[justapasserby]

Another best practice is to create multiple layouts in the Model view so that you can create the star (or snowflake) schema for each fact table in the data model. I have a model with 12 fact tables and the “All tables” view can get overwhelming. This makes relating fact to dimension tables much more manageable. Hiding fact tables is also a good practice, so that only dimension tables can be used to slice and dice the data.

[scottschuler]

Another helpful hint on top of the description is to hide the fields in the fact table that map to a dimension table like dates/products/customers/etc. That way the report writer only uses the fields from the dimensions that filter out both fact tables. Made that mistake too many times, even as the model creator and report writer in one.

[jecwonch]

However if you model it this way you are restricted in the Filter directions in case you want to filter one fact table to the other if it's specifically required. Only one flow from the fact to another can be set on 'Both' to avoid circular refrences

[Marcus-ivvi]

I've been searching for this solution for weeks. Now i got it. Thanks a lot!

[ameyraj]

Had same issue while using salesforce as source as needed different date filter for 2 reports, so used both method
1. Consolidated fact
2. Duplicated it
3. Had common date dimension but joining key was different for both fact
It worked but we don't have much flexibility while using salesforce as source.

Patrick it would be helpful if we can have your view while using salesforce as source like kind of video on do's n don'ts.

[kattyhatty]

Hey Patrick, you just described the exact journey I took to come to the same conclusion when I was first learning to use Power Bi. I love that I am now doing it right. You’re a legend.

[wademanis]

Patrick, this is awesome. Old Qlikview guy and dealing with multiple facts is kind of a PITA. I was curious to confirm that me linking a single calendar to multiple facts via a common date id was acceptable. My numbers looked good, but hearing you confirm my model is certainly helpful. Really like your approach to get to the point and make it short and sweet. Good work and thank you.

[zishiwu]

Thank you for creating informative, concise, and entertaining videos! I did not think it was possible to make a funny yet informative video about data modeling in Power BI, but you have raised my standards for what a good technical video should be. Also, your real-world examples are very helpful for people just starting their careers like myself. I used to make those consolidated fact tables that you mentioned, but now I know better.

[JMcLeodKC]

@6:30...I'm a data warehouse developer so thanks for the plug. Power BI is great but BI visualization is only as good as the model/schema behind it

[Daithi]

Anyone else watching this go "phew" when you realise you did it the way he recommends....I certainly did lol

[lf]

Respectfully, this is a really basic scenario and hard to get it wrong. I've been struggling with a model that has 5 fact tables, all connected to 6 different dimension tables PLUS calendar PLUS time period selector. So each fact table ends up having 6 relationships and, big surprise, most of them inactive and don't filter by the dims as expected. When I try to activate the relationship, Power BI tells me I need to deactivate some other relationship first because otherwise it would be ambiguous. "An Active relationship already exists between tables" Deactivate the existing relationship first". I ended up with a big redesign in the data warehouse, retiring some of the fact tables and squishing others together to eliminate the need for the dims, but it hardly seems like best

[davesmulders]

The consolidated table in this example isn't that bad really. In fact it's the best option as the 'group by' hints will be missed by the end users.
You just need a default value for the internet sales lines that reads 'Internet Sales' in the Employee column. Now you don't have to rely on end-users checking and actually noticing the hover-over on each measure. Also, you can just make separate measure columns in your table that only holds the sales value for each sales type. The size penalty is not that big, and it saves you loads of filtering in DAX.

After all, sales are sales. Just take some time to consolidate/structure the delivered columns, and the resulting model is much more useable for the end-user. Anything that the model doesn't enforce or facilitate is something they need to remember. And they don't.

[erickknackstedt]

Guilty of building the consolidated fact table. Had to jump through all the hoops mentioned. Lesson learned! Great video, thank you.

[mohanbabu]

Hi Patrick, thanks for the quick video with good explaination.
I have comment regards to handling multiple fact tables.
Instead of building circular data model, I think we can have link table which will contain the common dimension columns through which the relationships to other tables dimension tables can be done