Error in creating relationship

I could not create new relation ship between Product table and Product Budget Table and found circular dependency error. Theoretically, I should be able to create relationships with lookup table.

This is a good article on circular dependancy

you may have to go under the hood to figure it out but generally Power BI does not allow two calculated columns that contain measures that are also based on that table aka circular dependancy

does that help? if not can you post your pbix file. thank you

2 Likes

Somehow, I understand circular dependency but not too much extent. File is uploaded for your review. Financial Reporting Demo Model.pbix (777.8 KB)

Sorry, I was referring wrong column. But, it creates interest to dig circular dependency to some extent.

Hi Tanzeel - In addition to above article, also check the below one by SQLBI. In this they explain the difference between Values/Distinct in terms of Circular Dependency. Same is present b/w Summarize/SummarizeColumn.

As Product Budget is using SummarizeColumns(Product[Index]) that produce similar results as Values , hence causing the Circular dependency. To avoid it, use Distinct/SUMMARIZE instead.

Product Budget = 
ADDCOLUMNS(
    CROSSJOIN(
        DISTINCT( Products[Index] ), DISTINCT( Dates[Short Month] ) ),
        "2018 Budget", CALCULATE( [Total Sales], Dates[Year] = "2017" ) * 1.075 )

Product Budget = 
ADDCOLUMNS(
    CROSSJOIN(
        SUMMARIZE( Products,Products[Index] ), DISTINCT( Dates[Short Month] ) ),
        "2018 Budget", CALCULATE( [Total Sales], Dates[Year] = "2017" ) * 1.075 )

Thanks
Ankit J

2 Likes

Thanks @ankit. I check it. I was not using right function or in other case wrong table reference.

Hi @Tanzeel

Please mark any response as Solution if you issue is resolved