Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Avoid Circular Dependency in Power BI for Calculated Column

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])
Need Help
Show and Tell
  • v-cazheng-msft's avatar
    v-cazheng-msft
    5 years ago

    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.

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , a new column in cost table

     

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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"))
  • v-cazheng-msft's avatar
    v-cazheng-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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.