Search code examples
powerbirelationshipdaxstar-schema

Power BI why circular dependency is detected

Can you please explain why I run into this alert message of circular dependency when I try to create relationship between dimension #product (or #region) and a #bridge table which is a Cartesian of product x region?

I have connected #bridge with Sales and Budget by single column P@G witch is concatenation of product and region.

Download file here: PBIX

enter image description here

Solution

  • The solution is simple. Do not use CALCULATE function in the DAX bridge tables. Instead add all that columns to the same table later as calculated columns.

    I changed the original code of the bridge table which was:

    ADDCOLUMNS (
    CROSSJOIN ( '#product', '#region' ),
    "P@R", COMBINEVALUES("@",'#product'[product], '#region'[region]),
    "sales", CALCULATE ( SUM ( Budget[target] ) ),
    "IsSale", IF ( CALCULATE ( SUM ( Budget[target] ) ) > 0, "Yes", "No" )
)

    To something simpler:

    ADDCOLUMNS (
    CROSSJOIN ( '#prodact', '#region' ),
    "P@R", COMBINEVALUES("@",'#prodact'[product], '#region'[region])
)

    I modified the DAX code of bridge table so as to leaving only the columns necessary for joins. The columns that I needed to be calculated I added as calculated columns. And that's it. It was by pure chance I found that out while experimenting with it.

    For playing with bridge tables I recommend this Alberto Ferrari's article: https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/. It inspired me to solve the problem. What I get from the Alberto's text is that the functions VALUES and ALL are no good for bridge tables. He mentions issue of using CALCULATE function inside the bridge DAX tables. The function somehow is translated to mixture of ALL and FILTER functions. Instead of VALUE and ALL, use functions as DINSTINCT and ALLNOBLANKROW.

    Working PBIX file. Hurray!