Calculated column

[Anonymous]

Greetings,

Tables:

2016 Rebate
2017 Rebate
sales

I have this this Calculated column:

Rebate 2016b = CALCULATE(
SUMx(
Filter(
sales,
sales[year]=2016),
sales[Rebatable]*related('2016 Rebate'[Percent%])))

When I attempt to replicate it to access 2017 Values:

Rebate 2017b = CALCULATE(
SUMx(
Filter(
sales,
sales[year]=2017),
sales[Rebatable]*related('2017 Rebate'[percent])))

I get this error regarding circular dependency:

A circular dependency was detected: sales[Rebate 2017b], sales[Rebate 2016b], sales[Rebate 2017b].

Can you point me to the error of my ways???

Regards,

[DAX0110 · Accepted solution]

Hi @Anonymous,

I'd suggest one of two ways to work around this error:

(1) rewrite the calculated columns to remove the CALCULATE:

   Rebate 2016b = IF( sales[year]=2016,  sales[Rebatable] * related('2016 Rebate'[Percent%]) )

   Rebate 2017b = IF( sales[year]=2017,  sales[Rebatable] * related('2017 Rebate'[Percent%]) )

, or

(2)  remove these calculated columns from the Sales table and change them into measures.

[Anonymous]

Thank you - lots to learn, often quoted

"DAX is simple, but it is not easy!"

Best