Forum Discussion
How to fix circular dependency in two calculated columns using switch statement?
Hi vedantsri,
The reason why you're seeing a circular dependency error is that the DemandColumn and SupplyColumn calculated columns are referencing each other. To resolve this issue, you can create two separate measures for Demand and Supply instead of calculated columns.
Here's how you can do it:
Create the following measures:
EliteDemand = CALCULATE(SUM('DemandTable'[Value]), 'Office'[Tier] = "Elite")
MidDemand = CALCULATE(SUM('DemandTable'[Value]), 'Office'[Tier] = "Mid")
LowDemand = CALCULATE(SUM('DemandTable'[Value]), 'Office'[Tier] = "Low")
EliteSupply = CALCULATE(SUM('SupplyTable'[Value]), 'Office'[Tier] = "Elite")
MidSupply = CALCULATE(SUM('SupplyTable'[Value]), 'Office'[Tier] = "Mid")
LowSupply = CALCULATE(SUM('SupplyTable'[Value]), 'Office'[Tier] = "Low")Create two new measures for the Demand and Supply card visuals using the following formulas:
DemandColumn = SWITCH(SELECTEDVALUE('Office'[Tier]), "Elite", [EliteDemand], "Mid", [MidDemand], "Low", [LowDemand])SupplyColumn = SWITCH(SELECTEDVALUE('Office'[Tier]), "Elite", [EliteSupply], "Mid", [MidSupply], "Low", [LowSupply])
Add the 'Office' table to your report and create a slicer for the 'Office' column. This slicer will be used to select an office and update the Demand and Supply card visuals accordingly.
With these measures and formulas, you should be able to achieve your goal of having two card visuals, one for Demand and one for Supply, that display the values based on the selected office and tier.
Best regards,
Isaac Chavarria
If this post helps, then please consider Accepting it as the solution and give Kudos to help the other members find it more quickly
Hi vedantsri,
The reason why you're seeing a circular dependency error is that the DemandColumn and SupplyColumn calculated columns are referencing each other. To resolve this issue, you can create two separate measures for Demand and Supply instead of calculated columns.
Here's how you can do it:
Create the following measures:
EliteDemand = CALCULATE(SUM('DemandTable'[Value]), 'Office'[Tier] = "Elite")
MidDemand = CALCULATE(SUM('DemandTable'[Value]), 'Office'[Tier] = "Mid")
LowDemand = CALCULATE(SUM('DemandTable'[Value]), 'Office'[Tier] = "Low")
EliteSupply = CALCULATE(SUM('SupplyTable'[Value]), 'Office'[Tier] = "Elite")
MidSupply = CALCULATE(SUM('SupplyTable'[Value]), 'Office'[Tier] = "Mid")
LowSupply = CALCULATE(SUM('SupplyTable'[Value]), 'Office'[Tier] = "Low")Create two new measures for the Demand and Supply card visuals using the following formulas:
DemandColumn = SWITCH(SELECTEDVALUE('Office'[Tier]), "Elite", [EliteDemand], "Mid", [MidDemand], "Low", [LowDemand])SupplyColumn = SWITCH(SELECTEDVALUE('Office'[Tier]), "Elite", [EliteSupply], "Mid", [MidSupply], "Low", [LowSupply])
Add the 'Office' table to your report and create a slicer for the 'Office' column. This slicer will be used to select an office and update the Demand and Supply card visuals accordingly.
With these measures and formulas, you should be able to achieve your goal of having two card visuals, one for Demand and one for Supply, that display the values based on the selected office and tier.
Best regards,
Isaac Chavarria
If this post helps, then please consider Accepting it as the solution and give Kudos to help the other members find it more quickly
Thank you so much! This worked! But I believe this only works for a single selection on the slicer correct? If I wanted to do it fo multiple selections (where the results of each office are added up), how would I do that?
So for example, if selecting just office A gave the result of the SupplyColumn measure as 2 and then for another office selected B it gives 4, and so the card visual would show 4+2 so 6.
Glad to hear my solution worked!
To achieve the desired result of adding up the values for multiple selections on the slicer, you can modify the formulas for the DemandColumn and SupplyColumn measures in the following way:
DemandColumn = SUMX( VALUES('Office'[Office]), SWITCH('Office'[Tier], "Elite", [EliteDemand], "Mid", [MidDemand], "Low", [LowDemand] ) )
SupplyColumn = SUMX( VALUES('Office'[Office]), SWITCH('Office'[Tier], "Elite", [EliteSupply], "Mid", [MidSupply], "Low", [LowSupply] ) )
The key difference is that instead of using the SELECTEDVALUE function to get the selected value from the slicer, we use the VALUES function to get a table of selected values. We then use the SUMX function to iterate over this table and sum up the results of the SWITCH statement for each selected office.
With these modified measures, the DemandColumn and SupplyColumn card visuals will now show the aggregated values for all selected offices.
Best regards,
If this post helps, then please consider giving Kudos
Thank you so much for replying back!
When I tried this, it said 'cannot find 'Office'[Tier]'. When I use selected value function, it recognizes it. What might be the issue?
