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.
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.
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!
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.
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
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.
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.
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 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.
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.
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.....
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.
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!
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!
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.
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 :)
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
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.
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.
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.
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
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 👍
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)
@@KNP-BI I handle different granularities by using ISFILTERED, SUMMARIZECOLUMNS and TREATAS. You can use these to only show measures at the appropriate level.
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
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
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.
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!
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 :)
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?)
It clearly depends 😉 As usual, the software design will reflect the organizational structure. If internet sales and store sales are something very separate in the organization then separate smaller fact tables are reasonable. If the most frequent view on sales is holistic sales then a consolidated fact table will likely become reality. It's not that much of a difference for the end user whether you need to pull different measures to a visual or a measure and a legend category (internet/store) or a measure and a filter. Of course, in both cases, you would find the corresponding architecture in the data warehouse already. So sticking to the data warehouse architecture is the universal advice.
I agree completely. However there is a problem with following datawarehouse design. First you need both internet and retail sales in one datawarehouse ;)
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
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!
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.
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
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.
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?
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!
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.
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.
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.
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.
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!!!
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.
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!
To be honest, our DWH Is developed in a way that dimension that aren’t applicable get a specific key, so we can use 1 consolidated table that won’t give blanks as long as they are on the same fact subject. So we have a consolidated table for Retail, E-Com and Marketplace sales.
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.
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
Hi Patrick, yes I had this problem where I had to receive beneficiaries tables from different services and coming in different shapes (exactly like your example of retail and internet sales), but the number of different services sources was 8, so yes I used Power Query to append those tables in one table and things went fine. And I don’t agree that this is a bad idea because if I follow your way then I have to write complex DAX to count beneficiaries from all tables. I had all data in MS Access database, and I tried to create a union query from the database backend and then import it to Power Query, but guess what Power BI does not connect to union queries!!! Do you believe it?? What a petty, so I had to do it in Power Query, and as I told you, I found it the best possible way.
I found out that having multiple fact tables may not always be the best idea. The model is highly compressed so it doesn't matter for me that much if I have it one. IMHO it is easier to handle single fact table. All measures are consistent then. Also having multiple fact table one cannot use both way filtering. Not sure how it impact speed for user having multiple fact tables. Looking at how professional datawarehouse was organised - there was one main fact table. How many fact tables is too much?
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) .
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
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
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
Sometimes it does make sense to use consolidated fact table for instance I am working on a financials report that has a bookkeeping table and a sales table. The sales table did not have all the income and profit to give the whole picture, so I appended the missing accounts from the bk table to the sales table. Boom instant access to all data. BK table is missing some dimensions and I informed users that that means it comes from bk. Literally took me seconds, no need to think about any dax trickery, just sum on column. There is no need to separate the two otherwise in the report. The bk reporting has its on tabs that are a requirement. In this case only a few and not all accounts from bookkeeping needed to be included.
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?
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.
Sounds like a really good playlist "GuyInACube reads and applies designs from The Data Warehouse Toolkit" to their data models in PowerBI (maybe using PowerQuery)."
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?
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.
Nice one Patrick. But the trouble is the content creator who DOES NOT read that description and ends up creating a costly cross join query that can pull down the user experience of others. Hope there is a setting to "enforce" such group by constraints(Hope the Microsoft Power BI team is listening on)
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.
Maaan... I’ m a professional table “consolidator” LOL... Sometimes my dataset takes 2-3 hours to load... Will try this approach by creating the calendar table. Thanks man!
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.
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 ?
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?
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.
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?😂❤❤❤
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?
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?
Hi, thanks for this video, very interesting content, by the way is there anyone here that would mind explaining how can you create a measure that is coming from both fact tables ? i've been struggling with that issues for few week
You can use treatas if you have keys to relate them. Treatas function creates a relationship only the measure and not in the model. You can use it in a calculate function
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...?
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)
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)
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.
What if you have facts (or columns to summarize) on a dimension table that's not related to the main fact table? For example: imagine the product table has a column with the launch date for each product (which is not the same as the date of its first sale) and you wanted to plot these product launch dates together with sales to answer questions like - "how long does it take a new product to start selling?" - "what sales volumes do we typically see within the first year since launch?" - "how long (after launch) does it take for a product to pay back its development costs?" Is there a way to avoid having >1 date table in this scenario?
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?
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?
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.
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
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?
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!
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).
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?
![Kodak Black - Versatile 4 [Official Video]](http://i.ytimg.com/vi/VcP_1Ua__vY/mqdefault.jpg)
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.
I like how you use the Description property to list tables you can group by! Putting that one in my pocket!
I had been dealing with a broken report for almost two weeks and this literally saved my job!!!! Thank you
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.
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!
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.
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
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.
You can hide fact tables if you have factless fact tables, but if they contain calculaions, you need them.
How does multiple layouts affect relationships? Is there any indepth tutorial on this
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.
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.
Can u please give exapmles for this from Patrick's model (maybe from minutes 7:20 in the video) ? thank you!
@@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.
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.
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.....
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.
I have the same problem.
@@andrefreitas1572 yeah that is how we ended up solving the problem!
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!
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!
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.
@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
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 :)
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
Anyone else watching this go "phew" when you realise you did it the way he recommends....I certainly did lol
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
This was like day 3 of my Power BI journey way back when...with 18 fact tables :-D
Hahaha same!
Yup. I was secretly hoping there was a better way. My report consumers still don't use the consolidated fact tables properly
@@stuartmccloud307 Same! I have to write complex Dax to join 8 facts into a single measure
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.
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.
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.
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
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 👍
I've been searching for this solution for weeks. Now i got it. Thanks a lot!
you should address the fact that fact tables often have different level of granularity which makes thinks much harder I think
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)
@@KNP-BI I handle different granularities by using ISFILTERED, SUMMARIZECOLUMNS and TREATAS. You can use these to only show measures at the appropriate level.
@@richardcroft3676 Any performance issues regarding your approach so far? Thanks
@@DanielWeikert Not when using live connect to AAS with facts of 50m+ rows. If same performance applies on PBI models don’t know
Guilty of building the consolidated fact table. Had to jump through all the hoops mentioned. Lesson learned! Great video, thank you.
Me too. Last week I thought my consolidated fact table was the answer to my problem....., apparently not! Thanks Patrick!
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
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!
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
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.
YES consolidate facts where appropriate. My models have 4-7 FACT tables to mash up whatever.......its all about mashing
Not only was this helpful, it was super engaging and easy to follow. Y'all rock - thank you!
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!
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 :)
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!
Thank you very much for this video Patrick, I would like you to explain the slowly changing dimensions with an example in another video
must admit, I use your phrases sometimes to really explain the point to my peers and it works :) thanks Patrick
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?)
It clearly depends 😉 As usual, the software design will reflect the organizational structure. If internet sales and store sales are something very separate in the organization then separate smaller fact tables are reasonable. If the most frequent view on sales is holistic sales then a consolidated fact table will likely become reality. It's not that much of a difference for the end user whether you need to pull different measures to a visual or a measure and a legend category (internet/store) or a measure and a filter. Of course, in both cases, you would find the corresponding architecture in the data warehouse already. So sticking to the data warehouse architecture is the universal advice.
Any where to find good sources to learn about complex dimensional modeling that has more than 1 fact table?
I agree completely. However there is a problem with following datawarehouse design. First you need both internet and retail sales in one datawarehouse ;)
Problem number 1, never made that mistake. Problem 2, I did that one A LOT! I've learned from these great videos!
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
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!
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.
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
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.
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
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.
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?
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!
I also heard of that, Union or append is pretty common it seems and I would like to know more to
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.
I’m experimenting with using visual level filters with measures to remove blank values.
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.
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.
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.
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!!!
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.
@7:32...I love these features especially the first time I saw them in the original SSAS / MDX cube designer tools back in 2006ish
The visual effects are the video more funny and helps to understand the point. Nice improvement !
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!
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?
To be honest, our DWH Is developed in a way that dimension that aren’t applicable get a specific key, so we can use 1 consolidated table that won’t give blanks as long as they are on the same fact subject. So we have a consolidated table for Retail, E-Com and Marketplace sales.
Took me a while to get used to the shared Date tables, and understanding how table relationships worked in general.
What if you want to see the purchase of a customer from the Internet Sales?
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.
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
Hi Patrick, yes I had this problem where I had to receive beneficiaries tables from different services and coming in different shapes (exactly like your example of retail and internet sales), but the number of different services sources was 8, so yes I used Power Query to append those tables in one table and things went fine. And I don’t agree that this is a bad idea because if I follow your way then I have to write complex DAX to count beneficiaries from all tables.
I had all data in MS Access database, and I tried to create a union query from the database backend and then import it to Power Query, but guess what Power BI does not connect to union queries!!! Do you believe it?? What a petty, so I had to do it in Power Query, and as I told you, I found it the best possible way.
I found out that having multiple fact tables may not always be the best idea. The model is highly compressed so it doesn't matter for me that much if I have it one. IMHO it is easier to handle single fact table. All measures are consistent then. Also having multiple fact table one cannot use both way filtering. Not sure how it impact speed for user having multiple fact tables.
Looking at how professional datawarehouse was organised - there was one main fact table.
How many fact tables is too much?
Very thorough step-by-step how-to video. Thank you!
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) .
I really struggled with this until now - Thank you!
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
Great tip on using comments to communicate with other developers
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
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
Sometimes it does make sense to use consolidated fact table for instance I am working on a financials report that has a bookkeeping table and a sales table. The sales table did not have all the income and profit to give the whole picture, so I appended the missing accounts from the bk table to the sales table. Boom instant access to all data. BK table is missing some dimensions and I informed users that that means it comes from bk. Literally took me seconds, no need to think about any dax trickery, just sum on column. There is no need to separate the two otherwise in the report. The bk reporting has its on tabs that are a requirement. In this case only a few and not all accounts from bookkeeping needed to be included.
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?
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.
Brilliant! I knew using conformed dimensions was an option just had to be sure! Thank you
Sounds like a really good playlist "GuyInACube reads and applies designs from The Data Warehouse Toolkit" to their data models in PowerBI (maybe using PowerQuery)."
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
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?
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.
The group by description in the model is a great idea. Great stuff!
Nice one Patrick. But the trouble is the content creator who DOES NOT read that description and ends up creating a costly cross join query that can pull down the user experience of others. Hope there is a setting to "enforce" such group by constraints(Hope the Microsoft Power BI team is listening on)
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.
Maaan... I’ m a professional table “consolidator” LOL... Sometimes my dataset takes 2-3 hours to load... Will try this approach by creating the calendar table.
Thanks man!
❤ it and maybe the best advise is to add a description to your Tables, Fields and Measures
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.
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 ?
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.
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?
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.
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?😂❤❤❤
Brilliant! I love the way you explain it so logically.
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?
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?
Yes, easy if using shared aka conformed dimensions between the fact tables
Hi, thanks for this video, very interesting content, by the way is there anyone here that would mind explaining how can you create a measure that is coming from both fact tables ? i've been struggling with that issues for few week
You can use treatas if you have keys to relate them. Treatas function creates a relationship only the measure and not in the model. You can use it in a calculate function
@@eleftheriakoniari3392 But then you are jumping through the loopholes. I find it easier to write measures having data structured in one fact table.
Yes, have used conformed dimensions. Thank you 👍
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...?
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)
Hi, I’m facing this problem currently as well. Have you found a solution for this?
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)
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.
What if you have facts (or columns to summarize) on a dimension table that's not related to the main fact table?
For example: imagine the product table has a column with the launch date for each product (which is not the same as the date of its first sale) and you wanted to plot these product launch dates together with sales to answer questions like
- "how long does it take a new product to start selling?"
- "what sales volumes do we typically see within the first year since launch?"
- "how long (after launch) does it take for a product to pay back its development costs?"
Is there a way to avoid having >1 date table in this scenario?
Brilliant! I will attempt to apply it. Many thanks.
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?
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?
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.
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
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?
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!
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).
Hi Patrick, If we have two fact tables which contains date of purchase and occurance date then this approach will work ?
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?