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.

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

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

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

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

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

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

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

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

[@Marcus-iv2vi]

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

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

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

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

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

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

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

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

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

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

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

[@gioratamir9578]

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

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

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

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

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

[@erica.stalee1794]

It's a little strange that the problem goes from one data model (3:14) to the solution of a different data model. Initially we have two separate calendars for Internet and Retail. If the initial model is instead the given situation without a 'global' calendar existing, would a solution be to just append the Reseller Sales Calendar to the Internet Sales Calendar as a new 'Global' Calendar and then remove duplicates? Or would this slow down the model too much?

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

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

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

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

[@DanielWeikert]

you should address the fact that fact tables often have different level of granularity which makes thinks much harder I think

[@KNP-BI]

Agreed, I have that exact issue I'm working through right now. I either have to take the fact table down to a level of granularity that I was trying to avoid or build a bridging table or complex DAX. I'd love to see this video re-done with a much more complex model. (not taking away from the fact, it is very useful info for those starting out with data modelling)

[@richardcroft3676]

@@KNP-BI I handle different granularities by using ISFILTERED, SUMMARIZECOLUMNS and TREATAS. You can use these to only show measures at the appropriate level.

[@DanielWeikert]

@@richardcroft3676 Any performance issues regarding your approach so far? Thanks

[@richardcroft3676]

@@DanielWeikert Not when using live connect to AAS with facts of 50m+ rows. If same performance applies on PBI models don’t know

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

[@rlmillercpa]

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

[@awarrentfa]

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

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

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

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

[@annapechina452]

Hi! Nice video as usual :) I love the solution to keep tables as they are in the initial data warehouse. But once you start building measures like ARPU on top of both reseller and internet sales, isn't the appending solution better and easier to work with?

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

[@davidfunga]

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

[@julietzhang]

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

[@tomaskavicky]

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

[@dantepraxedis]

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

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

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

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

[@harkiratsingh571]

Great video Patrick and this is the kind of stuff which goes around so often. The key issue here is the tussle between Power BI and data warehouse team where sometimes the DW team is slow in implementing. In a lot of cases we even end up making the dimension table inside Power BI by duplicating, appending and removing duplicates in Power Query and then creating relationships with fact tables. This eventually slows down the model but at least gives a result. Any suggestion on how to make dimension tables inside Power BI effectively for a worse case scenario which follows the star schema design as showcased in the video?

[@hotboyphil]

a trick you could do is create a measure in a fact table and put in in the filter of your visualization to connect it to the other dimensions

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

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

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

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

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

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

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

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

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

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

[@Adrian-bw7gn]

I really struggled with this until now - Thank you!

[@khyathimanthena7861]

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

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

[@henrytnh1]

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

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

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

[@ankitmaheshwari6746]

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

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

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

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

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

[@MichalTyborski]

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

[@BlighHedges]

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

[@evelic]

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

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

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

[@ztymedina]

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

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

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

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

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

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

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

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

[@ZiggyBoon]

To me this issue is emblematic of the classic Inmon vs Kimball design approach. I can never remember which one is which, but starting with the optimized data mart that then populates the data warehouse rather than the other way around seems the more idealized approach. So in this example there would be a "sales data mart" structured in a way that a single fact table would have the relevant foreign keys pointing out to the dimensional tables that handle the differences in the how various channel sales are processed. This would, in theory at least, eliminate the need for the various tips & tricks to mimic a "conformed" design, such that the PBI report developer would simply need to pull in the various related tables and let PBI do its thing with "detect relationships". Sort of related: There's a SQL joke a DBA told me many moons ago that the complexity of a SQL statement required to get the data you need is proportionate to how bad your database design is. Funniest joke I ever heard!

[@homam4358]

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

[@gabrieleprovenza6805]

can i add description on analysis services metrics in some way?

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

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

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

[@roycemekolle7334]

Hi Patrick thanks for the video. I usually use a consolidated fact table to get to a dimension table. Let me explain; we don’t have a DW and I build reports based on downloaded data from our software. So I get out patient report and in patient reports. I use 1 calendar table but cuz I don’t have a list of doctors, I usually consolidate the two facts then extract a unique list of doctors and their corresponding specialties to use to slice my consolidated fact tables. In this situation is there some recommendations to make my model faster? Thanks

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

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

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

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