Circular dependencies and relationships - Unplugged #55

Avoid circular dependencies when calculated columns or tables are involved in regular one-to-many relationships.
Related articles: www.sqlbi.com/articles/blank-... www.sqlbi.com/articles/expand...
Read more about the "unplugged" format: www.sqlbi.com/blog/marco/2021...
#unplugged

[@tamerjuma]

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

[@VictorHugo-bd3bf]

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

[@joaoluismartins]

This video is GOLD! Thanks for that SQLBI.

[@sarajvo2010]

Video is about expanded tables. Great video

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

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

[@ihorkoropetskyi3234]

Very detailed explanation. Thanks

[@marilenadonofrio9787]

You're amazing, Marco!

[@ikar2k]

Absolutely amazing! Bravissimo!👍👌

[@joceirchaves316]

Amazing explanation about circular dependencies!

[@gustavobarbosa906]

Very interesting, Marco! Thank you!

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

[@mohammed333suliman]

Thank you, great explanation

[@johnveliku]

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

[@mabl4367]

Great video!

[@juliane1mota]

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

[@mrbartuss1]

This showed me how little I know...

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

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

[@brunof.s.8186]

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

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

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

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

[@Charlie888]