Circular dependencies and relationships - Unplugged #55

Avoid circular dependencies when calculated columns or tables are involved in regular one-to-many relationships.







#unplugged

[louism.]

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

[VictorHugo-bdbf]

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

[nishantkumar]

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.

[pro]

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

[sarajvo]

Video is about expanded tables. Great video

[businessinsights_AlexRobe]

Another GREAT concept and solution explanation Marco, thanks!

[joaoluismartins]

This video is GOLD! Thanks for that SQLBI.

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

[joceirchaves]

Amazing explanation about circular dependencies!

[johnveliku]

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

[brunof.s.]

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

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

[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

[julianemota]

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

[hervebessone]

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!

[coolblue]

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.

[riteshbarthwal]

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.