Handling MULTIPLE fact tables in Power BI

[@thesmoothman]

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

[@sarahvullo1892]

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.

[@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.

[@odianosenakhibi5144]

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

[@kattyhatty3799]

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.

[@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!

[@Marcus-iv2vi]

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

[@wademanis5784]

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.

[@zishiwu7757]

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.

[@RickTol]

Omg I'm a novice in power bi and spent my whole Friday doing different tricks to solve this problem and actually ended with this solution. Glad go know it's a effective way to do it. Funny how i get this video after struggling haha, but i still appreciate it

[@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

[@mohanbabu9939]

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

[@rlmillercpa]

Very thorough step-by-step how-to video. Thank you!

[@justapasserby69420]

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.

[@idonjohnson6999]

You can hide fact tables if you have factless fact tables, but if they contain calculaions, you need them.

[@noneB974]

How does multiple layouts affect relationships? Is there any indepth tutorial on this

[@avatorl_in_a_hammock]

they doesn't affect any relationship. they are just partial views of the single data model. and you still see the complete data model on 'All tables' page.

[@arturoperez231]

Thank you very much for this video Patrick, I would like you to explain the slowly changing dimensions with an example in another video

[@ameyraj4947]

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.

[@lf5781]

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 practice.....

[@andrefreitas1572]

Contact me, I can probably help you to fix your problem if you're still having it. Just to give a small tip: sometimes you need two relationships between a dim and a fact table, you can use the "userelationship" in DAX to active the non-active relantionship for certain measures.

[@CGProd-ov9mk]

I have the same problem.

[@lf5781]

@@andrefreitas1572 yeah that is how we ended up solving the problem!

[@ronaldj35]

Thank you very much Patrick. It is very helpful with the difficulties I just have now. This solves presenting data in reports but I always have difficulty with measures using both fact tables

[@JMcLeodKC711]

@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

[@chrismengel3176]

Just implemented this approach on my most recent dashboard. Actually ended up with 3 fact tables and they each connected to the respective dimension tables. Works like a champ!

[@datawhisperer7383]

Hi Chris, Does your model beside shared dimensions, include at least one non shared dimension? If yes, did you create a slicer on that non shared dimension? if yes, does you slicer filter a second fact? similar to what Patrick has in his enhanced model Dim Employee to filter Internet Sales for instance ? Thanks for your feedback!

[@henrytnh1]

Brilliant! I will attempt to apply it. Many thanks.

[@npaler01]

The group by description in the model is a great idea. Great stuff!

[@Daithi354]

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

[@stuartmccloud307]

I was like 'yay, I'm doing it the right way' then like 'crap, I thought there was going to be a much better way of doing it' lol

[@mikaganszauge9591]

This was like day 3 of my Power BI journey way back when...with 18 fact tables :-D

[@shamsuddinjunaid30]

Hahaha same!

[@mattorange]

Yup. I was secretly hoping there was a better way. My report consumers still don't use the consolidated fact tables properly

[@grantmckinnon4733]

@@stuartmccloud307 Same! I have to write complex Dax to join 8 facts into a single measure

[@awarrentfa]

Not only was this helpful, it was super engaging and easy to follow. Y'all rock - thank you!

[@dantepraxedis]

The visual effects are the video more funny and helps to understand the point. Nice improvement !

[@scottschuler2234]

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.

[@MrTC-rv3jo]

Can u please give exapmles for this from Patrick's model (maybe from minutes 7:20 in the video) ? thank you!

[@leppyr64]

​@@MrTC-rv3jo The Product table is related to Internet Sales and Reseller sales on the Product Field. They are suggesting that you hide the product field in Internet Sales and Reseller Sales so that those fields don't get accidentally selected instead of Product in the Products table. As you can see, Patrick has already hidden all fields on the fact tables other than the measures.

[@EMB3D]

I love these real life scenarios, this is exactly what i face now. I don't know how you two do this mind reading, but keep it up!

[@erickknackstedt3131]

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

[@TainuiaKid1973]

Me too. Last week I thought my consolidated fact table was the answer to my problem....., apparently not! Thanks Patrick!

[@CreateYOUTV]

BRO! Thank you for this. Was beating my head against a wall until I watched this. Won’t even go into details. Just thank you dude!

[@Adrian-bw7gn]

I really struggled with this until now - Thank you!

[@fivefournino]

Need to try this as we have a similar problem for sales forecasting. We have a target table which has regions, segments, dates, and products, but then separate product and deal tables holding different levels of details. We need to show progress to target by regions, segment, and products all separately requiring multiple relationships. Hopefully some region, product, and segment bridge tables like this help solve these challenges.

[@alibaroroh9355]

Thank Patrick... its also solve my problem when I am want compare target sales (column Date, value target, product category) data from excel with actual sales in power BI. Tk

[@demris15]

Love this as a best practice... I've been stuck on creating my own keys for my model and populating those keys in the necessary tables, but i would have certainly made a few of these mistakes

[@JMcLeodKC711]

@7:32...I love these features especially the first time I saw them in the original SSAS / MDX cube designer tools back in 2006ish

[@arbazahmad7177]

Great 👍 idea 💡 Patrick.. for handle multiple fact tables.. thanks 😊

[@tomaskavicky]

must admit, I use your phrases sometimes to really explain the point to my peers and it works :) thanks Patrick

[@datawhisperer7383]

Great thanks Patrick for shading light on this multi-facts with shared & non shared dimensions PBI model. On your enhanced model, you have an Employee dimension that filters only Reseller Sales fact as this is expressed by a one to many relationship My concern is how to cascade this to the Internet Sales fact, in other words how to make the employee filter both facts in a consistent way knowing there is no employee candidate field in the Internet sales fact table. I am not sure but I heard the TREATAS can resolve this bug. How? It could be by adding the TREATAS within a CALCULATE. Power BI has a powerful DAX engine and I believe this can be resolved by an advanced DAX formula. Thanks again for sharing tips!!!

[@jenniferowens3443]

You ever figure out your question about how to cascade the employee relationship over to the other fact table? I have that issue with a model I am currently working on.

[@MG-217]

Very useful video , thank you !

[@aliciapeel9561]

Thanks! Been trying to find a resource like this for ages as the data I work with is very messy and I seem to always have multiple fact tables. Looks like I have some unlearning to do!

[@aliciapeel9561]

In other news, I'm looking for more resources on linking to Salesforce Objects, could you do a video on this or direct me to some good resources?

[@ajayi789]

Brilliant! I knew using conformed dimensions was an option just had to be sure! Thank you

[@lyonelremond4075]

A lot of good and sound advice here! Even as an experienced professional, it's good to see other people explaining it with other (better!) words & ideas. The final trick of using the description field / tooltip feature to let people know how to use the fields properly is a clever yet simple workaround. I particularly the way you explain the correlation of unrelated factors (great name by the way!). In fact, I might reuse it to explain it to me people. As for the "consolidating tables" that's another great explanation with an example. I see being done all the time! Another great video from you guys! Keep up the great work 👍

[@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 :)

[@AmritaOSullivan]

Brilliant! I love the way you explain it so logically.

[@sujusuresh]

Great video. Simple and to the point. But this is a very common scenario.😀

[@CrazyFunCricket]

awesome man.. Please mark more videos on how to approach data model creation

[@patricklin5266]

Exactly what I need. Thanks!

[@BIInfinite99]

Confirmed Dimensions/ Shared dimensions/ common Dimensions are always best to use. Even if there is no proper data warehouse built. At least try creating calendar dimension with in power BI using min and Max from the required date fields to have a common dimension and handle the measures accordingly. It’s better to always avoid French toast 😀 from customers @5:03

[@jonathanshields2016]

Solid video - thanks

[@davidfunga]

Problem number 1, never made that mistake. Problem 2, I did that one A LOT! I've learned from these great videos!

[@virginia6622]

Very helpful video! Thank you. Tgus subject is very important, could you having more about Thanks again!

[@zxccxz164]

great that someone finally discussed conforming dims...however i also have situations where i need multiple dims.....ship_cal, order_cal , invoice_cal....the reason is at details grains i can pull in specfic full name attributes....SHIP MONTH, ORDER MONTH for column and rows and can add them both to visuals. All that and still have conforming dims where appropriate. I see no dev or speed perf for using USE RELATIONSHIP in EVERY single measure

[@MrLaresh]

Sir the video was very informative, I have one question what if we have two fact tables with one similar Column but there no there is no Dim Column to connect these both fact tables with.

[@RonaldPostelmans]

great explanation

[@tinapan4111]

Brilliant Video! Love it!

[@omermirza5994]

You are awesome! Thank you!

[@RushiLadani1995]

Thanks this is great content! Thank you.

[@365cloudhouse7]

I love the French toast, great that you don't use that terrible word. thank you. I don't quite understand it yet. Are there two tables in the database? 1 for sales orders via reseller 1 for sales via internet Or does this arise from duplicating queries in the query editor? Pleasant manner of presentation you have.

[@abdullahkhan-on7sb]

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.

[@SamStrife]

And here I am mind blown that you can create different layouts in the relationship screen and drag and drop the fields from the side! Need to look into that some more as I didn't realise it was a thing and I don't dare open up my relationship page anymore, it's just too scary to look at!

[@alfredsfutterkiste7534]

yes hella great. Also remember you can right click on a table and 'add related tables' to automatically... add the related tables to your current view :)

[@sirjlogan]

Great subject because I’ve been stacking two fact tables. However, your solution for me needed you to slow down at the end and show what measures to use to distinguish the two types of sales.

[@mohamedbouniar]

Thank you for this amazing video

[@hk_200k]

Quick follow up: how about if we have 10 teams sales and 10 tables, and we need to report "total sales"? (The Dim Table/ relationship are good, like what Patrick said) Do we drag them into visual one by one, or we just make a dax measure: Total sales = Sales1+Sales2+Sales3.... and use the dax

[@_theessenceoflife1021]

Hi Thank you for the great video! I have a question. I Have two fact table and I have two FYTD sales from each table and want to put it into a single graph but they seems to be not connected. I have a same data structure as the 7:24 of the video. Can someone help me? (If my question itself is too vague please let me know..)

[@liad87alkobi]

The consolidated fact tables is the best practice performance wise. As long as the data is similar in his structure it is best to consolidate. Then make different partitions for refresh (for the different data source\type) .

[@BlighHedges]

Took me a while to get used to the shared Date tables, and understanding how table relationships worked in general.

[@karimaki643]

Hi patrick and others as well! What about if I have two facts sharing multiple same dimensions, but I would need cross-filtering to narrow down user selections (dimensions)? If the cross-filtering is on, then the connections between dimensions and the second fact go inactive. If cross-filtering is off, then this schema works, but users see awful many choices e.g. in product groups, even if they only need to see products manufactured e.g. during last few weeks. This would be filtered by calendar, but now it doesn't work since cross-filtering is off.

[@mehdihammadi6145]

❤ it and maybe the best advise is to add a description to your Tables, Fields and Measures

[@stephanbaero]

How do you recommend going about cross-filtering in your example? I found myself introducing bi-directional relationships until - boom - the model blew up with a circular reference. Then I opted for the consolidated table where I could filter as I wanted. Use case: Compare today’s sales planning to the one from last year to see what has changed.

[@christaulbee6402]

DAX is the answer here. Create DAX calculations that give you last years sales values and then you can create another measure that is the difference between the currently selected year minus the previous year for the delta.

[@mizzyconstruction4710]

Do you have a video with best practices on something like Accounts Payable modeling? For instance, a typical AP database will contain a header table for the invoices, then a detail table for the invoice lines, and then another table for payments made against those invoice lines, and yet another table for retainage released against those invoice lines. I can't seem to find any guidance on the best way to build a model with tables like this. Thank you!

[@denniscangroii3799]

Another plus of using the conformed dimensions is that you can do a direct query on the fact tables and import/refresh on the dimensions.

[@prolf2024]

Excellent ! Thank you smartest Charles Barkley brother

[@DubbG213]

Doesn’t Ralph Kimball recommend to “Sort Merge” the two or more fact tables (Full Outer Join) using the conformed dimension keys across both tables? Essentially doing as you showed in the second ‘bad’ method? More of a question than anything else. You guys are great and have helped me out of many sticky PBI situations!

[@rodneychan1227]

I also heard of that, Union or append is pretty common it seems and I would like to know more to

[@christaulbee6402]

I think this is the best case solution when you have 2 tables that hold the exact same data just in different grains (Header and detail tables) but not when the data is similar, but ultimately different. I think Patrick was just pointing out that it makes little sense to append tables if there will be blank values for half of the data for the reasons he mentioned and I agree.

[@Milhouse77BS]

I’m experimenting with using visual level filters with measures to remove blank values.

[@khyathimanthena7861]

Hey Patrick, could you let me know how to show a dynamic pop up on click of row in table in powerbi desktop

[@hk_200k]

Exactly what I'm looking for! Anything more about this? Hi Patrick, are you here? How to add sales up from two fact tables into one matrix column? Patrick you here?😂❤❤❤

[@BobbyRauch]

Just what I needed, good timing!

[@FlipTheCatOfficial]

When I do this the connections/relationships show as inactive for one fact table and active as the other. Yours show as active for both

[@dulguunalison4463]

Thank you. I have a more complex case that I'd like to clarify, Patrick. What if there are three fact tables within a composite model sourced from three separate PowerBI datasets in direct query mode, each with substantial data and an incremental refresh setup? How should the relationships between these three fact tables be managed, considering each has its own DateTable? My proposed solution was to designate one of the fact tables as the primary pillar and integrate the date columns from the other two fact tables into the DateTable of the primary fact table.

[@karannchew2534]

Hi Patrick, I feel that it's only half of the solution. Now that the fact are in separated tables, how to bring the two set of data together in my analysis / calculation? Would you make a videos to demonstrate please?

[@vishalnasre1251]

In Addition to this Can we create a duplicate/reference of the Employees table and rename them like Employee_Internet_Sales and Employee_Reseller_Sales Then connect Employee_Internet_Sales to Internet_Sales Employee_Reseller_Sales to Reseller_Sales By this, the end-user will also get to know that to filter out one particular sales table what column he should use from which table. Will this work...?

[@tareksalha]

In addition to describing the users, what dimensions they can use with a measure I really like to also create some dax rules, that return blank or an error, if the measure is used with a non-suitable dimension. ISFILTERED is a good candidate for example.

[@PeaceCannon]

Video becomes useful at 6:47. Needed to see the DAX on the formulas though. Been left with a video that says "And now it just works"... and I have no idea how it works.

[@GuyInACube]

Thanks for the feedback. The DAX formulas are SUM(fact[column]) for each of the fact tables. What makes this works in the relationship between both fact tables to a single dimension table. Please let us know if you have further questions.

[@Alex-ol4bv]

Great, I just started creating a new model when I ran into this exact problem. I had already started creating a consolidated fact table but after watching this video I then remodelled it and now all the DAX is much less complicated. However, in some use cases consolidating facts might still be the better alternative, especially in financial reporting or when a clean star schema is needed.

[@zxccxz164]

YES consolidate facts where appropriate. My models have 4-7 FACT tables to mash up whatever.......its all about mashing

[@antoninagibson3677]

Thank you very much for the helpful video, Patrick! I have a question re shared date calendar. What happens if I need two different date filters on a report page? One filtering 1st fact, another filtering 2nd fact table independently, but in a single visual.

[@TainuiaKid1973]

Great video. How do you create a measure that spans two fact tables? e.g. one fact table holds revenue and the other costs. Is [Profit] = [Revenue] - [Costs] the solution?

[@Milhouse77BS]

Yes, easy if using shared aka conformed dimensions between the fact tables

[@GreatRailJourneysAds]

outstanding content

[@MilanoBeastPL]

Wow, It's one of the best thing. I have an ocean of data and I had to create huge fact table from more than 10 tables with about 700,000 of rows in each other. In this solution an average refreshing time has been rapid decreased! I

[@williamcspereira]

Thank you!

[@milig.8106]

Thanks for the video! How about if we need to multiply values in two columns of unrelated tables? cant relate them because dont have unique values that match. how do we handle this using Dax? Thanks!

[@ztymedina]

How did he do Product & Employee tables ya? Did he consolidate the two fact tables & then create ID & create a new dimension table?

[@julietzhang]

What do you recommend when I have a lot of date columns that I want to use with a date table?

[@sofiamartino2186]

Hey guys! thank you so much for this info!! but, what if I'd like to know which products were purchased in both internet and retail channel? I mean, I'd like the list of products that were in both channels and all its attributes, say, in a table? I built the model just the way you told us to, but can't figure out how to perform this requirement. If I build a dimension "Internet" and "retail", and relate to a field in each fact table, then it becomes ambiguous with the calendar table. Thks in advance!!

[@minntheinaung]

That’s the same as I think a proper way to write DAX and reduce refresh time even though my organization recommends single fact table. Thanks for sharing such a logic that proves my assumptions right.

[@nicholassagan397]

Thank you! 🙏

[@thejonath]

How would you address related fact tables? Say you have a fact table with orders and one for deliveries, and they both have a key towards each other.

[@horseshoecrabs2000]

I'm struggling with this too. You could have a shopping cart for example with multiple item lines (line 1 is widgets, line 2 is fidgets, etc.), and each of those items can ship partial amounts until the line item is fulfilled. Unlike the video, these are 1:many fact tables. How is such a thing handled properly?

[@christaulbee6402]

I'd like to see a video or hear what others are doing with "Header" and "Detail" tables (2 tables with the exact same info, just different grains). There are a lot of databases I work with that have tables like this and I'm curious to know what everyone else is doing with these? Just ignoring the header? Merge it with the detail to get the relevant info?

[@timgray7859]

I just grab the info from the header table and place it onto the detail table, it's usually just a few relevant fields that I need; I'd rather pay the slightly longer refresh time than to impose that on all my DAX calculations, plus I get a clean fact table with all the necessary primary keys to my other dimensions.

[@Bharath_PBI]

Yes, have used conformed dimensions. Thank you 👍

[@timgray7859]

Are there any performance implications of using 2+ fact tables with common dim tables instead of combining them in power query into one bigger table? Assuming no granularity issues and simple SUMX statements in measures where you are combining them together? My thoughts are around whether it is more efficient for the engine to read one large fact table or 2+ smaller ones (maybe concurrently?), in addition to possibly having a shorter refresh time due to less transformations going on in the back end.

[@gumball5837]

Coming from the Qlik World i'm going to answer from the Qlik perspective which is quite similar to PowerBI. You get better performance in the report view when combining 2 large +fact tables (assuming they have same granularity level) as you have less distance to travel between tables due to table reduction. Fact table concatenation (Qlik equivalent to power bi append) is always the way to go when handling large volumes

[@virgolee2209]

I saw this video a while ago and didnt think much of it since I thought I would never run into those problems. And guess what, today I had to search for this one and watch it again to see where I did wrong. I have a small question though: what if your product table (which is dimension table) keeps growing overtime and it becomes very large that you need to do incremental refresh on it, do you still apply that method in the video and considering it dimension table or it becomes fact table and might need to apply different strategy?

[@volcanoRavi]

Beautiful video..Patrick

[@dafran500]

Great video! I am doing this model but, how do you link the dates? My date column on both tables have hour and minutes also. I need to create an specific key?