Handling MULTIPLE fact tables in Power BI

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

[@thesmoothman]

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

[@odianosenakhibi5144]

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

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

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

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

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

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

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

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

[@garrettfox1]

Why do Marco and Alberto at SQLBI, THE world experts on DAX, seem to never offer much guidance at all about Multiple Fact Tables across their entire body of work? Kimball also doesn't offer much guidance... Is there such a thing as too many fact tables for example? I'm thinking of separating mine into 1 fact table for data sources that need to be refreshed quarterly, 1 fact table for data sources that need to be refreshed monthly, 1 fact table for data sources that need to be refreshed weekly... That way I can avoid unnecessary refreshes and speed up refresh time of the data model.

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

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

[@Marcus-iv2vi]

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

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

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

[@101Avids]

Why is PowerBI not considered a data warehouse? You can import data, load it, transform it, model, report, snapshot, set up incremental refresh, etc. How is it different from a data warehouse? Or does MS just need to distinguish between Synapse and PowerBI / Azure Analysis Services? (Apparently Azure Analysis Services uses the same modeling engine as PowerBI?)

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

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

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

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

[@gioratamir9578]

What if you want to see the purchase of a customer from the Internet Sales?

[@davesmulders3931]

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.

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

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

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

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

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

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

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

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

[@rlmillercpa]

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

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

[@sandeepbarge4699]

I have Date Dimension, Product Dimension and they join on multiple fact tables. One of my fact has descriptive field too. And when I pull product name from product table, multiple measures from multiple fact tables and that descriptive attribute from fact table, it creates cartesian product. For each row in product table, it shows all the combinations of that descriptive column. How do I avoid it?

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

[@awarrentfa]

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

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

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

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

[@carlocalingasan998]

How about when I have 2 fact tables but the other table has a more granular facts. For example, a sales order which has order number, customer info, sales date and then a sales order detail which contains the order number, items ordered, price, quantity and individual ship date? I usually merge this in power query. Bad idea?

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

[@Adeelkhalid17]

Hey Patrick! I have fact tables with different date granularity so some of the facts are yearly and other are quarterly. user want to create a report where they can have quarter slicer but when they choose let say 2023-Q1 then the facts that don't have quarterly data show blanks whereas they expect it to show 2022 year end data for these facts. Is there any modeling solution to this problem?

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

[@corylowe5557]

What if you wanted to "union" the tables. For my use case, I want to do a "distinct count". So if you wanted to do a distinct count of retailer and internet market sales (my use case is user names), would that be slow performance and how would I do that in dax?

[@SamuelRoshan]

Hi Patrick, I have the exact same Multifact model built that you showed in the end. It works great if you use a Measure and add them into a Visual. But the visual breaks the moment I remove the Measure and only keep Columns from both Fact tables (Table visual for instance). This is the error I get "Can't Display the data becuse Power BI can't determine the relationship between two or more fields." Why so? Just wanted to understand.

[@MurtazaPashaaKhan]

Hi there, I hope you are doing fine. In my current project, I am using Power BI. I have data for daily phone calls, daily texts, daily contracts sent, daily offers sent, daily contracts signed, etc. I am recording these data against Regions and Agents. So, I have three dim tables (date, agent lookup, and state lookup) and many narrow fact tables. Now, when I'm trying to connect my date table with these fact tables, it lets me create 1-to-1 relationships, and sometimes 1-to-many relationships, even sometimes I see they selected filter directions to 'both.' My question is what will be the relationship cardinality to connect these fact tables of daily measures to the date table?

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

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

[@SunnyGoklani9]

Thanks! When I create the common table and link (1:many) to both - 1 becomes active, but the other table relationship is inactive. When I try to activate it, it says " You can't create a direct link...indirect relationship already exists...deactivate indirect relationships first" - what should I do? The indirect link is coming I think coz there's a field linked b/n 2 tables I'm connecting these too (for another variable - which needs to stay)

[@veltmanos]

Hi, I’m facing this problem currently as well. Have you found a solution for this?

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

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

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

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

[@dantepraxedis]

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

[@manatpluto]

okay i think i'm missing something. I'm using data from azure devops so my fact tables are... linked. so i can't link both of them to my dim tables. Is there another way to do it? sorry, i'm a beginner in power bi so it may be simple but i spent the last two days looking for a solution... can somzone help please? 😅

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

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

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

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

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

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

[@julietzhang]

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

[@rays3761]

Can I get some help here. We are using Power BI to help with reporting when we move to our new ERP system soon and I have been tasked with creating the report to handle the reporting. Currently I am Consolidating historical data, for example, sales from our previous system are being merged with sales from our new system with a column that specifies which source the data comes from for troubleshooting. I decided on consolidating after testing out keeping the facts tables separate. My 1-page test rebuilding using separate facts tables was painful for me as each DAX formula for the measures were very intricate and took a lot of work to make functional. I just want to check if I am okay to continue the consolidated route, I am close to the point of no return as about half of this huge report has been built using consolidated tables for historical data (and records not pulled over such as inactive items are also being consolidated to 1 table).

[@khyathimanthena7861]

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

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

[@ankitmaheshwari6746]

Hi Patrick, If we have two fact tables which contains date of purchase and occurance date then this approach will work ?

[@kevinmorneault3327]

hi is there a way to represent 2 table with different data in a clustered bar chart individually base on selected table. Basically if i select table1 result would show in clustered chart if i sleect table 2 it would show result in clustered bar chart

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

[@kawalpreetsingh548]

I want to create a dashboard that allows me to check the completeness of all the dashboards in my workspace after they are refreshed. For example, expected average number of rows for dashboard A and dashboard B is 10k and 20k, and dashboard "A" shows 10500 rows, I would consider it as complete and dashboard "B" has 2500 rows after refresh, I would consider it as a completeness issue. Wherever there is a completeness issue, I would want to trigger email to the key stakeholders using PowerAutomate that dashboard has technical issues and is under investigation. Basically, it's a health check of all the dashboards within PowerBI server workspace. Please suggest possible solutions.

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

[@davidfunga]

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

[@orafaelgf]

Great video. Doubt: I have 2 fact tables as well. I until getting split the data columns to others dimensions but my columns (id product and name product (e.g.)) aren’t uniques/distincts. How can I headling with that?

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

[@tomaskavicky]

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

[@aniketsurve620]

How about keeping both the fact tables disconnected and then connecting it to the respective date / dimension using dax variables . I have been doing this with a model that has 3 fact tables and the performance is better then the connected data model.

[@phlppnsk]

So quick question on that: What if I have have multiple fact tables that connect to different levels of my dimension table? To simplify it, an example: I have fact table 1 which gives me Sales, Cost, whatever on my products and I have another fact table 2 with KPIs which can only be calculated on the product group level. If I have a product dimension table (Product Group > Product) I cannot build a 1 to many relationship from that one to fact table 2. Is it "ok" in that case to build a single direction many-to-many relationship? Should I build some sort of bridge table? Should I get rid of the consolidated fact table 2 and build all these KPIs within the report with (in some cases really complex) DAX? What's the best practice there?

[@Adrian-bw7gn]

I really struggled with this until now - Thank you!

[@UlyssesHaq]

What do i do when i have files with Snapshot month column, but for each snapshot row, but then there is a full year calendar forecast of months horizontally across columns. Ive considered USERELATIONSHIP but it seems labour intensive formula wise. My ownly solution was two calendar tables to enable users two slice by snapshot and calendar AND snapshot independently.

[@zxccxz164]

YES!!! i am soooo tired of people saying just use USERELATIONSHIP....it is LABOR intensinve and way more work writing potentially hundreds of measures or just add a second or third small calendar table with all the options

[@melindamermaids]

What if you need to see both metrics from the 2 different fact tables side by side in a table then this won't work. Also doesn't this create a loop in your data model? For this reason, I always try to merge fact tables first.

[@patrickbcox]

What if I need to create a drill through report that shows for example all transactions on a single day or all transactions for single product? I don't think I can do that with separate fact tables.

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

[@homam4358]

Hi, how to create the consolidated data table "colander" in Power bi?

[@pasaripraneeth7321]

Hey Patrick, thanks alot for such a informative videos I had learned alot. I just have a quick question what if my multiple fact tables as same dimensions columns but if I related them together it creates a many to many relationship. To avoid that is it best practices to create a single dimension table and related them as one to many relationship ?

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

[@eleftheriakoniari3392]

Hello Patrick! I take my data from our live sql server. And my model has multiple fact tables. I can join together some of them but still I have at least 2 fact tables from the live db and 1 from dwh that I want in the same model. How can I make my schema? Connect each fact table only with the dim tables or use a table in the middle and connect each fact table with it and the middle table to the dim tables?

[@evelic]

How can you create the dimension filtering tables from the fact tables?

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

[@ztymedina]

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

[@VR-jf2py]

Hi, can you please help me here, When user open a report and if the dataset refresh for that report is in progress then display message that report data refresh is still in progress, thanks

[@BlighHedges]

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

[@renatorodriguesmedeiros9569]

Hey Patrick...i now have a calendar table that connects to dates in both fact tables...but when I try to connect another dimension (like product), the field that both tables have..I cannot...Power BI attest that are ambiguity and the relationship will not be enforced...any idea why this is happening?

[@Fox050]

Does this work with 3 or more fact tables? How to handle ambiguities? I want active relationships between these fact tables and other dimensions

[@yonisupersaiyanyoni3693]

You need to do measure if you want data from both fact in the same visual? You can't drug and drop field from both to same visual?

[@alishehzadpaul]

Can we connect two different data sources using Direct Query? For example, I want to connect a table from a Teradata server, and another table from an Oracle database (assuming both tables have a common key)

[@kalyanidronamraju8939]

Hi, I tried to join 1 consolidated dimension(month) with 2 fact tables as you did. The relationship is showing as dotted line and one of the fact join is not active. Can you help me resolve that?

[@FrancuzALaMenes]

Hi #guyinacube I have a problem creating a visualisation in PBI, a table to prepare graphe. I have items in my data table, those are projects, each has a planned closure date and an actual closure date and a saving hours amount. I need to show that in one visualisation which shows per month how many hours were planned to be saved and against it the actuals...😅 Please help, seams simple, but can't find a solution...

[@yashjk899]

Hy hi But if we have 1 field from the first fact table then it will filter only it but at same time if I want a 1 column that I can add in the filter to filter both table

[@coreysheerer8199]

Thanks for the video, i have a specific question about using a dimension table to connect multiple fact tables. How can one handle multi-grain fact tables (aka fact tables at different resolutions). For example, if i have a Location Dim table with columns State and County, and two fact tables. The first fact table has data on the Country level. The second fact table has data only on the State level. How can i use the dimension table to connect to both of those fact tables and get accurate output of the measures? The issue is, the fact table on the state table has a many to many relationship with the Location Dim table state column. To note, i started in Cognos, and this is fixed by a property called 'Determinants'. Thanks in advanced!

[@arbazahmad7177]

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