Circular dependency error in switch(true)

[summer18]

I need help on resolving circular dependency error on my formula:

Sales Level =
SWITCH(TRUE(),
'Sales'[Sales by Product]>=1000000,"Level1",
'Sales'[Sales by Product]<=999999 && 'Sales'[Sales by Product]>=500000,"Level2",
'Sales'[Sales by Product]<=499999 && 'Sales'[Sales by Product]>=100000,"Level3",
"Level4"
)

The formula for "Sales by Product" which was used in column above:

Sales by Product =
SUMX(
          KEEPFILTERS(VALUES('Sales'[ProductID])),
          CALCULATE(SUM('Sales'[Actual]))
           )

Hoping for any assistance.

[sowjanya123]

Hi,

I need help on resolving circular dependency error on my formula:

Below is the calculated column: 

Retro Test Support =

SWITCH(TRUE(),

[FraudPoint LY Revenue]>=100000 && Fraudpoint_Customers[Customer Type Flag]=0,"National Support",

[FraudPoint CY Revenue]>=10000 && Fraudpoint_Customers[Customer Type Flag] = 1,"Strategic Support"

)

FraudPoint CY Revenue is a measure : 

FraudPoint LY Revenue = CALCULATE([Sum of LY Revenue],FILTER(Fraudpoint_Customers,DISTINCTCOUNT(Fraudpoint_Customers[customer_account_sk])))

Sum of LY revenue is also a measure:

Sum of LY Revenue = CALCULATE([Total Revenue], FILTER(ALL('Date'), 'Date'[Closed Revenue Index by Year]=-1))

Total Revenue is also a measure:

Total Revenue = CALCULATE(SUM('fact_revenue_usage'[Total Billed Charges]),FILTER(Customers,DISTINCTCOUNT(Customers[customer_account_sk])))

Looking forward for help on how to fix the circular dependency error coming in Retro Test Support calculated column

[PijushRoy]

Hi @summer18 

Please try this
Sales Level =
SWITCH(TRUE(),
'Sales'[Sales by Product]>=100000,"Level3",
'Sales'[Sales by Product]>=500000,"Level2",
'Sales'[Sales by Product]>=1000000,"Level1",
"Level4"
)

If not work, please share sample data or pbix file.
If solve your requirement, please mark this answer as SOLUTION
If this comment helps you, please LIKE this comment/Kudos

[summer18]

Hi @PijushRoy , it did not work.  I think I have to remove the circular dependency on ProductID but I don't know how.

[bcdobbs · Accepted solution]

Hi @summer18 ,

Is Sales by Product a calculated column? If so try this instead:

Sales by Product =
CALCULATE (

    SUM ( 'Sales'[Actual] ),

    ALLEXCEPT( 'Sales'[ProductID] )

)

Have a read of 

https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/

Which explains why you get circular dependency.

[summer18]

thank you @bcdobbs , it works!

[PijushRoy]

Hi @summer18 
Please paste some sample data