How to fix circular dependency in two calculated columns using switch statement?

[vedantsri]

Hi All!

Firstly thank you to anyone helping out!

So I have this office table:

Office	Tier
A	Elite
B	Mid
C	Mid
D	Low

Now I wanted to get Demand and Supply numbers for these offices that is coming in from Demand and Supply tables from my model respectively (Their content is probably not needed for my problem).

Since I want the demand and Supply numbers be calculated differently for each tier, I created 1 Demand measure and 1 supply measure for each of the office tiers (So in total 6 measures: 3 demand measures and 3 Supply Measures).

To apply the demand measures to each office depending on their tier, I created a calculated column called demand with the following formula: 

DemandColumn = Switch('Office'[Tier], "Elite", EliteDemandMeasure, "Mid", MidDemandMeasure,"Low",LowDemandMeasure)

The resulting table looks like:

Office	Tier	DemandColumn
A	Elite	200
B	Mid	150
C	Mid	150
D	Low	100

NOW, when I tried to create a calculated column for supply called SupplyColumn with the following formula:

SupplyColumn = Switch('Office'[Tier], "Elite", EliteSupplyMeasure, "Mid", MidSupplyMeasure,"Low",LowSupplyMeasure)

Office	Tier	DemandColumn	SupplyColumn
A	Elite	200	Error
B	Mid	150	Error
C	Mid	150	Error
D	Low	100	Error

This gave me an error saying there is circular dependancy between my two new calculated columns Demandcolumn and SupplyColumn. Is their any way to fix this?

My main goal is to have two card visuals; One for Demand and one for Supply that would have the two calculated columns applied to them respectively. When a person makes a selection on a 'Office' Slicer that I have created (Which will have the first column from my office table), the Demand and Supply card visuals would use my two calculated columns to rerieve the value. If anyone has a better way of doing this, please let me know!

Thank you,

Vedant

 

[ichavarria · Accepted solution]

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:

1. 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")

2. 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])
   
   

3. 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

[vedantsri]

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.

[ichavarria]

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 

[vedantsri]

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?