Circular dependencies and relationships - Unplugged #55

[@louism.4980]

These guys are incredibly smart, thank you so much Marco!

[@tamerjuma]

So far this is the best explanation of circular dependency. Many thanks Marco for sharing your amazing knowledge. ❤

[@lucasdenker3734]

One of the best channels there is about Power BI. Love how you guys can make complex concepts into easy to understand lessons! Do you guys have any video explaning about how to make relationship between two facts tables without having issues on performance of the report? I'm having trouble on my recent models where i need to do just that

[@SQLBI]

You should always use dimensions to connect different fact tables. The best proxy article is this one: www.daxpatterns.com/budget/

[@VictorHugo-bd3bf]

This is gold. Thank you, Marco, for this fantastic explanation of the concept.

[@nishantkumar9570]

Thanks so much for the insightful explanation for this circular dependencies problems. Now, I have pretty clear idea about the problem and how to fix this. Thanks so much.

[@joaoluismartins]

This video is GOLD! Thanks for that SQLBI.

[@sarajvo2010]

Video is about expanded tables. Great video

[@businessinsights_AlexRobe]

Another GREAT concept and solution explanation Marco, thanks!

[@pro1427]

omg you must have a crystal ball or something, faced this issue yesterday tryng to make a dimension table using UNION and VALUES functions (combining 2 direct query tables). Of course i faced the "circular dependecy" error, just replaced the VALUES for DISTINCT and solved the issue. Thank you very much master!!

[@marilenadonofrio9787]

You're amazing, Marco!

[@gustavobarbosa906]

Very interesting, Marco! Thank you!

[@joceirchaves316]

Amazing explanation about circular dependencies!

[@ikar2k]

Absolutely amazing! Bravissimo!👍👌

[@kkaurkify]

Thank you Finally I got an explanation why I have had to redo the calculated column using the filter function and could not use the calculate function.

[@ihorkoropetskyi3234]

Very detailed explanation. Thanks

[@mwaltercpa]

Great video Marco. Context trans question: At 24:00 is it fair to imagine that the filters converting all col (including expanded col) into filer context are hidden VALUES() filter in CALCULATE for each column reference? VALUES honors the blanks, and that leads us to remove those filters? Thanks again!

[@marcorusso7472]

Not really. The context transition gets the row context (on Sales) and transform it into a filter context over the expanded table Sales. However, the filter is a table that has one row and many columns of different tables. Imagine this (simplified): VAR FilterFromContextTransition = TREATAS ( { Sales[Net Price], Sales[PriceRangeKey], RELATED ( PriceRange[PriceRangeKey] ), RELATED ( PriceRange[Min] ), RELATED ( PriceRange[Max] ) }, Sales[Net Price], Sales[PriceRangeKey], PriceRange[PriceRangeKey], PriceRange[Min], PriceRange[Max] ) RETURN CALCULATE (..., FilterFromContextTransition ) The CALCULATE receives a filter that has one row and several columns - some of them could be blank, but that is not the direct reason why you see the circular dependency.

[@mwaltercpa]

This helps me, I appreciate the detailed explanation. Thank you Marco. Looking forward to the new offerings this year.

[@hervebessone2759]

A HUGE THANK YOU for this video and all(noblankrow 😂) the others by the way. You solved the problem that puzzled me for two days. What a coincidence!

[@johnveliku]

My God... I mean Marco Russo is my God :). Great infos!

[@juliane1mota]

Thank you for the amazing video!!! Helped me so much! ❤

[@laionegan]

Thanks Marco.I don't understand when you say that calculate triggers context transition but we are creating a calculated column so there is no filter context, there is only a row context.so what remains in the context transition?anyway i have to review better that part with removefilters

[@SQLBI]

The row context becomes a filter context because of the context transition triggered by CALCULATE.

[@mohammed333suliman]

Thank you, great explanation

[@coolblue5929]

Thanks for showing us how to fix this, I am confused about the reasoning though. You are saying that we need to avoid having a function that depends on the table on the one side. How is DISTINCT ( PriceRanges[PriceRangeKey] ) not dependent on PriceRanges[PriceRangeKey]?? It seems that you are saying that the root issue is referential integrity. If the key is connected by the relationship, and therefore included in the expanded table, then the value from PriceRanges is already there and no query on the calculated table is required. If the key in the fact table is not in PriceRanges[PriceRangeKey], then the engine queries PriceRanges. I don’t get why it needs to do that. I guess the high level takeaway is to ensure no RI errors.

[@SQLBI]

The dependency is on the BLANK value that is included if the relationship is invalid. Check out www.sqlbi.com/articles/understanding-blank-row-and-limited-relationships/ and www.sqlbi.com/articles/blank-row-in-dax/

[@mabl4367]

Great video!

[@anilkapkoti296]

Amazing ❤

[@brunof.s.8186]

This is one of those videos where my only reaction is 🤯

[@riteshbarthwal5024]

Hello can somebody pls explain me the code on 26min:58sec. In first two filter context we are applying filter on priceranges table and in 3rd filter context we are removing filter from entire table priceranges.

[@SQLBI]

The filter removal takes precedence in CALCULATE - see dax.guide/CALCULATE/

[@riteshbarthwal5024]

@@SQLBI Thank you, really appreciate!

[@riteshbarthwal5024]

@@SQLBI hello team can you please explain this topic on Whiteboard sessions?

[@riteshbarthwal5024]

@@SQLBI sorry team, but need one more clarification for the same code. Can you please how is the value of variable currentprice changing its value in each row context. Looking for your response or anyone who can guide me on this.

[@mrbartuss1]

This showed me how little I know...

[@Charlie888]