Sala de Sinais

Handling MULTIPLE fact tables in Power BI

* O link para o vídeo completo está no final desse artigo.
Saiba mais clicando em Robô IQ OPTION de AUTOMATIZAÇÃO de Lista de SINAIS
E conheça também sobre o Melhor Bot Para Opções Binárias 2021
Que tal um robo gratuito para IQ Option? Sistema Para IQ Option Grátis

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

Understand star schema and the importance for Power BI

📢 Become a member:


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

🎓 Guy in a Cube courses:


🛠 Check out my Tools page –

#PowerBI #DataModel #GuyInACube


35 Comentários

  1. I have a report with 7 fact tables, tried to connect them all to a calendertable, but the connection doesnt work. Can somebody help.

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

  3. 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!!!

  4. This is actually practical. So many videos just say use star schema and I would if I could! Anyone else has any other recommendations about video about alternate data model patterns?

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

  6. Thank you Patrick as always! How do you create those beautiful animations in your video?

  7. I have 3 fact tables – connected to a factless fact table – now have to introduce the Datedim. Is there any good way to do this plZ?

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

  9. 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?

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

  11. I think the 2021 year started in a big looping PBI ideas/tricks/cheats … HAHHA LOL

  12. Does having loops in your data model cause potential issues like how it does in other reporting tools?

  13. Suppose I have 2 fact tables, Purchases and Shipments. They share dimensions such as date and customer. The business key of Purchases is PurchaseId. The Shipments table has the PurchaseId, too. If I want to see the status of a shipment for a given purchase, how do I do that without a fact to fact join?

  14. But what if you need to filter all fact tables by a dimension that only exists within one of the fact tables? In this example, you couldn't filter by the employee dimension. I have a case where i need to filter between 5 dimensions across 4 fact tables.

  15. 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!

  16. Another great video! What if I have 3 fact tables (sales, sales target and revenue)? Can I connect the dimension tables to the third fact table just as you did with the two fact tables? Thank you.

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

  18. 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!

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

  20. What if the fact tables have different date ranges? Do you create a calendar table with a default date range i.e 1990 to 2050? And not using the dates max and min from the fact tables. How does that then affects the time intelligence measures?

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

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

  23. 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)

  24. Not really sure, how this solution will 1 consolidated value coming from 2 different fact tables unless I merge the queries.

  25. 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 ?

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

Comentários estão fechados.