Avoid Circular Dependency in Power BI for Calculated Column

[Anonymous]

I have two tables "Report Images" and "Cost". Both does not have any relationships. Both tables are in below structure.

ReportImages 

Image URL          Indicator

Cost Table

Indicator             Other Cost Details

I tried to create a calculated column in Cost table taking the column "ImageURL" from "Report Images" table matching the Indicators column in both tables. It is giving me circular dependency error.

Please help. I need to create only calculated column and not a measure.

Below is the query I have used.

Calculated Column = CALCULATE(VALUES(ReportImages[ImageURL]),FILTER(ReportImages,ReportImages[Indicator] = Cost[Indicator])

[v-cazheng-msft · Accepted solution]

Hi @Anonymous 

You can try the following steps.

1 Make some changes to the Calculated column ‘COSTIndicatorColor’

COSTIndicatorColor =

var res1=CALCULATE(SUM(Invoices[TOTAL_AMOUNT]),FILTER(Invoices,Invoices[ProjectUID]=PlannedPayment[ProjectUID]),DISTINCT(Invoices[ProjectUID]))

var res2=CALCULATE(SUM(PlannedPayment[PlannedPayment]),ALLEXCEPT(PlannedPayment,PlannedPayment[ProjectUID]))

var res=res1-res2

return IF(res < -15,"#FF0000",IF(res >= -15 && res < -5, "#D3D3D3","#33AF81"))

2 Create a Calculated column

Calculated Column = CALCULATE(VALUES(ReportImages[ImageURL]),FILTER(ReportImages,ReportImages[Indicator]=PlannedPayment[COSTIndicatorColor]))

Here is the pbix.

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , a new column in cost table

maxx(FILTER(ReportImages,ReportImages[Indicator] = Cost[Indicator]),ReportImages[ImageURL])

[Anonymous]

@amitchandak  No still it show the same.

[Anonymous]

@amitchandak Cost[Indicator] is also another calculated field which I created in the report.

Indicator = IF([COSTMeasure] < -15,"#FF0000",IF([COSTMeasure] >= -15 && [COSTMeasure] < -5, "#D3D3D3","#33AF81"))

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

@amitchandak Please find the below link to download the pbix file. Check for the table planned payment and inside that I have tried to created a calculated column which gives circular dependency error.

https://1drv.ms/u/s!Ah-kO6CqeTNOgh0FAF1T4GD22ocK?e=E0g2Ph

Please help.