topic Re: Cannot able to RETURN multiple VAR conditions in DAX calculated column in DAX Commands and Tips

Cannot able to RETURN multiple VAR conditions in DAX calculated column

Anonymous — Fri, 08 Nov 2024 05:34:38 GMT

Hi Experts,

Below is the DAX i am working on it and in that you can see multiple VAR is created to link each other (result will change based on the previous VAR condition) . But after written all the VAR conditions I cannot able to get the expected results.

Calculated Column DAX :

Cost Status =

VAR CostSpentYTD = 'Test Delivery Updates (2)'[Cost Spent YTD]

VAR PlannedCostYTD = 'Test Delivery Updates (2)'[Planned Cost YTD]

VAR OverallBudgetedCost = 'Test Delivery Updates (2)'[Overall Budgeted Cost]

VAR WhatIsStillNeeded = 'Test Delivery Updates (2)'[What is still needed to complete the project?]

VAR IsBudgetOverrun = 'Test Delivery Updates (2)'[Is the budget overrun due to customer dependency/CG side?]

VAR IsCRRaised = 'Test Delivery Updates (2)'[Is the CR raised for this additional cost?]

VAR IsCRApproved = 'Test Delivery Updates (2)'[Is the CR approved for this additional cost?]

VAR DipInMargin = 'Test Delivery Updates (2)'[Is there a dip in margin?]

VAR IsSOWSigned = 'Test Delivery Updates (2)'[is the SOW signed?]

VAR OutstandingInvoices = 'Test Delivery Updates (2)'[Is there any outstanding invoices to be raised?]

VAR YTDActualFTE = 'Test Delivery Updates (2)'[YTD Actual FTE Release]

VAR YTDPlanFTE = 'Test Delivery Updates (2)'[YTD Planned FTE Release]

VAR YTDActualprocess = 'Test Delivery Updates (2)'[YTD Actual process deployed count]

VAR YTDPlanprocess = 'Test Delivery Updates (2)'[YTD Planned Process deployed count]

VAR catchup = 'Test Delivery Updates (2)'[Is there a catch up plan available?]

VAR Status1 =

IF(

CostSpentYTD > PlannedCostYTD * 0.1,

"Red",

IF(

CostSpentYTD < PlannedCostYTD * 0.1,

"Amber",

"Green"

)

VAR Status2 =

IF(

OverallBudgetedCost < CostSpentYTD + WhatIsStillNeeded * 1.1,

"Red",

IF(

OverallBudgetedCost < CostSpentYTD + WhatIsStillNeeded,

"Amber",

"Green"

)

VAR FinalStatus =

SWITCH(

TRUE(),

IsBudgetOverrun = "Customer" && (Status1 = "Red" || Status2 = "Red"), "Amber",

IsBudgetOverrun = "CG" && (Status1 = "Red" || Status2 = "Red"), "Red",

IsCRRaised = "Yes",

IF(Status1 = "Red" || Status2 = "Red",

SWITCH(Status1, "Red", "Red", "Amber"),

SWITCH(Status1, "Amber", "Amber", "Green")),

IsCRApproved = "Yes","Green",

IsBudgetOverrun = "Customer" && IsCRRaised = "No" && IsCRApproved = "No", "Red",

DipInMargin < -3, "Red",

DipInMargin < 0, "Amber",

DipInMargin > 0,"Green",

IsSOWSigned = "No", "Amber",

IsSOWSigned = "No" && Status1 = "Red", "Red",

OutstandingInvoices = "Yes" && Status1 = "Green", "Amber",

OutstandingInvoices = "Yes", "Amber",

OutstandingInvoices = "Yes" && Status1 = "Red", "Red",

ABS(YTDActualFTE-YTDPlanFTE) > YTDPlanFTE * 0.1, "Red",

ABS(YTDActualFTE-YTDPlanFTE) < YTDPlanFTE * 0.1, "Amber",

ABS(YTDActualprocess-YTDPlanprocess) > YTDPlanprocess * 0.1,"Red",

ABS(YTDActualprocess-YTDPlanprocess) < YTDPlanprocess * 0.1,"Amber",

YTDActualFTE = YTDPlanFTE,"Green",

YTDActualprocess = YTDPlanprocess,"Green",

catchup = "Yes","Amber",

catchup = "No","Red",

"Green" // Default fallback color

)

RETURN

FinalStatus

Expected vs Original with all inputs

Account Name	xxxxx Club	xxxxx Inc.	xxxx Financial	xxxx - Brazil	xxxx \| India	xxxx Plc	yyyy Depot	yyyy GRAPHIC	yyyy pak	yyyy guard	yyyy Inc	zzzz Corporation	zzzz well
Offer Type	RPA	RPA	RPA	RPA	RPA	RPA	RPA	RPA	RPA	GenAI	RPA	RPA	RPA
Overall budgeted cost	100	100	90	100	100	100	100	100	90	100	100	100	100
Planned cost YTD	100	100	80	100	100	100	100	100	80	101	100	100	100
Cost Spent YTD	125	125	100	125	125	125	125	105	100	105	125	90	90
What is still needed to complete the project?	10	10	10	10	10	10	10	2	10	2	10	9	10
if the "YTD Cost spent" is more than 10% of "YTD Planned Cost", it should be Red, if less than 10%, it is amber, if not spent is green	25%	25%	25%	25%	25%	25%	25%	5%	25%	4%	25%	-10%	-10%
if the "Overall budgeted cost" is less than "Cost Spent YTD"+"What is still needed to complete the project?" by more than 10% then red, if it is less than 10% and more than 0%, then amber, otherwise green	35%	35%	22%	35%	35%	35%	35%	7%	22%	7%	35%	-1%	0%
Is the budget overrun due to customer dependency/CG side?	CG	Customer	CG	CG	CG	CG	Customer	Customer	Customer	Customer	CG
Is the CR raised for this additional cost?	Yes	No		Yes	Yes	Yes	Yes				Yes
Is the CR approved for this additional cost?	No	No		Yes	Yes	Yes					Yes
Is there a dip in margin?				2%	2%	4%		0			2%
Is the SOW signed?				Yes	No						Yes
Is there any outstanding invoices to be raised?				Yes							No
Expected	Red	Red	Red	Amber	Amber	Amber	Amber	Amber	Amber	Amber	Green	Green	Green
Dashboard Status	Correct	Amber	Correct	Red	Red	Red	Correct	Correct	Correct	Correct	Red	Correct	Correct

Above table is the sample account names and inputs on all the columns .
Few results are expected and correct one but few accounts has wrong results.

Please help to fix those pending account results.

let me know if need more info.

Thanks

Re: Cannot able to RETURN multiple VAR conditions in DAX calculated column

VahidDM — Fri, 08 Nov 2024 06:15:17 GMT

Hi Anonymous

I think your calculated column isn't returning the expected results because the percentage calculations in Status1 and Status2 are incorrect. You're comparing absolute values instead of calculating the percentage difference between costs.

Solution:

Modify your Status1 and Status2 variables to correctly compute the percentage difference. Here's the corrected DAX code:

Cost Status = VAR CostSpentYTD = 'Test Delivery Updates (2)'[Cost Spent YTD] VAR PlannedCostYTD = 'Test Delivery Updates (2)'[Planned Cost YTD] VAR OverallBudgetedCost = 'Test Delivery Updates (2)'[Overall Budgeted Cost] VAR WhatIsStillNeeded = 'Test Delivery Updates (2)'[What is still needed to complete the project?] VAR IsBudgetOverrun = 'Test Delivery Updates (2)'[Is the budget overrun due to customer dependency/CG side?] VAR IsCRRaised = 'Test Delivery Updates (2)'[Is the CR raised for this additional cost?] VAR IsCRApproved = 'Test Delivery Updates (2)'[Is the CR approved for this additional cost?] VAR DipInMarginText = 'Test Delivery Updates (2)'[Is there a dip in margin?] VAR DipInMargin = VALUE(SUBSTITUTE(DipInMarginText, "%", "")) // Convert percentage text to number VAR IsSOWSigned = 'Test Delivery Updates (2)'[is the SOW signed?] VAR OutstandingInvoices = 'Test Delivery Updates (2)'[Is there any outstanding invoices to be raised?] VAR YTDActualFTE = 'Test Delivery Updates (2)'[YTD Actual FTE Release] VAR YTDPlanFTE = 'Test Delivery Updates (2)'[YTD Planned FTE Release] VAR YTDActualprocess = 'Test Delivery Updates (2)'[YTD Actual process deployed count] VAR YTDPlanprocess = 'Test Delivery Updates (2)'[YTD Planned Process deployed count] VAR CatchUp = 'Test Delivery Updates (2)'[Is there a catch up plan available?] /* Corrected Percentage Calculations */ VAR PercentageOver1 = DIVIDE(CostSpentYTD - PlannedCostYTD, PlannedCostYTD, 0) VAR Status1 = IF( PercentageOver1 > 0.1, "Red", IF( PercentageOver1 < -0.1, "Amber", "Green" ) ) VAR TotalCostNeeded = CostSpentYTD + WhatIsStillNeeded VAR PercentageOver2 = DIVIDE(TotalCostNeeded - OverallBudgetedCost, OverallBudgetedCost, 0) VAR Status2 = IF( PercentageOver2 > 0.1, "Red", IF( PercentageOver2 > 0 && PercentageOver2 <= 0.1, "Amber", "Green" ) ) /* Adjusted FinalStatus Logic */ VAR FinalStatus = SWITCH( TRUE(), (IsBudgetOverrun = "Customer" || IsBudgetOverrun = "CG") && (Status1 = "Red" || Status2 = "Red"), IF(IsBudgetOverrun = "Customer", "Amber", "Red"), IsCRRaised = "Yes" && (Status1 = "Red" || Status2 = "Red"), Status1, IsCRApproved = "Yes", "Green", IsBudgetOverrun = "Customer" && IsCRRaised = "No" && IsCRApproved = "No", "Red", DipInMargin < -3, "Red", DipInMargin < 0, "Amber", DipInMargin >= 0, "Green", IsSOWSigned = "No" && Status1 = "Red", "Red", IsSOWSigned = "No", "Amber", OutstandingInvoices = "Yes" && Status1 = "Red", "Red", OutstandingInvoices = "Yes" && Status1 = "Green", "Amber", ABS(YTDActualFTE - YTDPlanFTE) > YTDPlanFTE * 0.1, "Red", ABS(YTDActualFTE - YTDPlanFTE) <= YTDPlanFTE * 0.1, "Amber", ABS(YTDActualprocess - YTDPlanprocess) > YTDPlanprocess * 0.1, "Red", ABS(YTDActualprocess - YTDPlanprocess) <= YTDPlanprocess * 0.1, "Amber", YTDActualFTE = YTDPlanFTE, "Green", YTDActualprocess = YTDPlanprocess, "Green", CatchUp = "Yes", "Amber", CatchUp = "No", "Red", "Green" // Default fallback color ) RETURN FinalStatus

Explanation:

Correct Percentage Calculations:

PercentageOver1: Calculates the percentage difference between CostSpentYTD and PlannedCostYTD.
PercentageOver2: Calculates the percentage difference between TotalCostNeeded and OverallBudgetedCost.
Adjust Status1 and Status2:

Use the calculated percentages to determine "Red," "Amber," or "Green" statuses based on your specified thresholds.
Convert Text to Numbers:

For fields like DipInMargin, remove percentage symbols and convert text to numeric values using VALUE and SUBSTITUTE.
Review and Adjust FinalStatus:

Ensure conditions are correctly ordered and logically structured.
Use SWITCH with TRUE() for better readability.
Check that each condition accurately reflects your business logic.
Result:

By correcting the percentage calculations and refining the logic in your FinalStatus, the calculated column will now produce the expected results for all accounts.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

LinkedIn|Twitter|Blog |YouTube

Re: Cannot able to RETURN multiple VAR conditions in DAX calculated column

Anonymous — Fri, 08 Nov 2024 07:32:47 GMT

VahidDM Thanks for your support and correction Vahid 🙂

I can see still most of the accounts are not produced the expected results but old DAX did .. below the comparison between old and updated DAX results across accounts.

Green background is expected where Red background produced with the new DAX .

It may be related to SWITCH conditions , if previous conditions are matching then the same color results are keep applying to next condition also i guess ..

Please help me to differentiate those conditions in the finalstatus variable ?

Re: Cannot able to RETURN multiple VAR conditions in DAX calculated column

Anonymous — Fri, 08 Nov 2024 14:13:42 GMT

VahidDM I found that which one is applying into all the accounts.
from here it is not jumping to next conditions ..
even dipinmargin = 0 then it should check for further conditions as well but it is not ..

Re: Cannot able to RETURN multiple VAR conditions in DAX calculated column

Anonymous — Mon, 11 Nov 2024 05:26:35 GMT

Hi Anonymous

From the DAX you have shared you can see that multiple fields from table 'Test Delivery Updates (2)' are referenced in that calculated column, which makes it not easy for us to understand and build sample data for testing.

Could you please provide some sample data that completely covers your problem?

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Cannot able to RETURN multiple VAR conditions in DAX calculated column

Anonymous — Mon, 11 Nov 2024 06:47:24 GMT

Anonymous Hey Jarvis , Could you please check for the latest comments and then you can able to find my query .? I have already applied some correction based on VahidDM solutions and then i am facing some SWITCH condition based issues which is affecting my results in the table ..

I have already shared the sample table data in the trail post with inputs and expected results ..

Re: Cannot able to RETURN multiple VAR conditions in DAX calculated column

Anonymous — Mon, 11 Nov 2024 07:24:20 GMT

Hi Anonymous

I did pay attention to your previous comment, in fact, the “Expected vs Original with all inputs” you shared in the original post did not cover all the fields used in the DAX of your calculate column, as shown in the screenshot below, and I don't understand these fields, so I'm having trouble constructing the sample data for testing.
Thank you for your understanding.

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Cannot able to RETURN multiple VAR conditions in DAX calculated column

Anonymous — Mon, 11 Nov 2024 09:58:07 GMT

Anonymous Ahhh yes sorry , below the one ..

Account Name	A	B	C	D	C	D	E	F	AA	BB	CC	DD	EE	FF	G	H	I	J	GG	HH
Overall budgeted cost	100	100	90	100	100	100	100	100	90	100	100	100	100
Planned cost YTD	100	100	80	100	100	100	100	100	80	101	100	100	100
Cost Spent YTD	125	125	100	125	125	125	125	105	100	105	125	90	90
What is still needed to complete the project?	10	10	10	10	10	10	10	2	10	2	10	9	10
if the "YTD Cost spent" is more than 10% of "YTD Planned Cost", it is Red, if less than 10%, it is amber, if not spent is green	25%	25%	25%	25%	25%	25%	25%	5%	25%	4%	25%	-10%	-10%
if the "Overall budgeted cost" is less than "Cost Spent YTD"+"What is still needed to complete the project?" by more than 10% then red, if it is less than 10% and more than 0%, then amber, else green	35%	35%	22%	35%	35%	35%	35%	7%	22%	7%	35%	-1%	0%
Is the budget overrun due to customer dependency/CG side?	CG	Customer	CG	CG	CG	CG	Customer	Customer	Customer	Customer	CG
Is the CR raised for this additional cost?	Yes	No		Yes	Yes	Yes	Yes				Yes
Is the CR approved for this additional cost?	No	No		Yes	Yes	Yes					Yes
dip in margin?				2%	2%	4%		0			2%
SOW signed?				Yes	No						Yes
Is there any outstanding invoices to be raised?				Yes							No
YTD Actual FTE Release														85	95	100
YTD Planned FTE Release														100	100	100
if the "YTD Actual FTE Release" is less than 10% of "YTD Planned FTE Release", it is Red, if between 10% and 0, it is amber, else green														15%	5%	0%
YTD Actual process deployed count																	85	85	95	100
YTD Planned Process deployed count																	100	100	100	100
if the "YTD Actual process deployed count" is less than 10% of "YTD Planned Process deployed count", it is Red, if between 10% and 0, it is amber else green																	15%	15%	5%	0%
catch up plan available?																	NO	YES
Expected	Red	Red	Red	Amber	Amber	Amber	Amber	Amber	Amber	Amber	Green	Green	Green	RED	Amber	Green	RED	Amber	Amber	Green
Dashboard Status	Correct	Amber	Correct	Red	Red	Red	Correct	Correct	Correct	Correct	Red	Correct	Correct	Green	Green	Correct	Green	Green	Green	Amber

Re: Cannot able to RETURN multiple VAR conditions in DAX calculated column

Anonymous — Tue, 12 Nov 2024 07:20:06 GMT

Anonymous Did you get any chance to check this ?

Re: Cannot able to RETURN multiple VAR conditions in DAX calculated column

Anonymous — Tue, 12 Nov 2024 09:41:24 GMT

Hi Anonymous

Sorry for the late reply.
Unfortunately I have not found a solution yet. In the SWITCH() function, the order of conditions matters. As soon as one value matches, the corresponding result is returned, and other subsequent values aren’t evaluated. So maybe some of these conditions are not being applied successfully.
As follows, when I changed the order of the conditions in your DAX, the results changed.

_Cost Status = VAR CostSpentYTD = 'Test Delivery Updates (2)'[Cost Spent YTD] VAR PlannedCostYTD = 'Test Delivery Updates (2)'[Planned Cost YTD] VAR OverallBudgetedCost = 'Test Delivery Updates (2)'[Overall Budgeted Cost] VAR WhatIsStillNeeded = 'Test Delivery Updates (2)'[What is still needed to complete the project?] VAR IsBudgetOverrun = 'Test Delivery Updates (2)'[Is the budget overrun due to customer dependency/CG side?] VAR IsCRRaised = 'Test Delivery Updates (2)'[Is the CR raised for this additional cost?] VAR IsCRApproved = 'Test Delivery Updates (2)'[Is the CR approved for this additional cost?] VAR DipInMargin = 'Test Delivery Updates (2)'[dip in margin?] VAR IsSOWSigned = 'Test Delivery Updates (2)'[SOW signed?] VAR OutstandingInvoices = 'Test Delivery Updates (2)'[Is there any outstanding invoices to be raised?] VAR YTDActualFTE = 'Test Delivery Updates (2)'[YTD Actual FTE Release] VAR YTDPlanFTE = 'Test Delivery Updates (2)'[YTD Planned FTE Release] VAR YTDActualprocess = 'Test Delivery Updates (2)'[YTD Actual process deployed count] VAR YTDPlanprocess = 'Test Delivery Updates (2)'[YTD Planned Process deployed count] VAR catchup = 'Test Delivery Updates (2)'[catch up plan available?] VAR Status1 = SWITCH( TRUE(), CostSpentYTD > PlannedCostYTD * 1.1, "Red", CostSpentYTD < PlannedCostYTD * 0.9, "Amber", "Green" ) VAR Status2 = SWITCH( TRUE(), OverallBudgetedCost > CostSpentYTD + WhatIsStillNeeded * 1.1, "Green", OverallBudgetedCost < CostSpentYTD + WhatIsStillNeeded, "Red", "Amber" ) VAR FinalStatus = SWITCH( TRUE(), catchup = "Yes", "Amber", catchup = "No", "Red", DipInMargin < -3 , "Red", DipInMargin < 0, "Amber", IsCRRaised = "Yes" && (Status1 = "Red" || Status2 = "Red") , Status1, IsCRApproved = "Yes", "Green", IsBudgetOverrun = "Customer" && IsCRRaised = "No" && IsCRApproved = "No", "Red", IsSOWSigned = "No" && Status1 = "Red", "Red", IsSOWSigned = "No", "Amber", ABS(YTDActualFTE - YTDPlanFTE) > YTDPlanFTE * 0.1, "Red", ABS(YTDActualFTE - YTDPlanFTE) < YTDPlanFTE * 0.1, "Amber", ABS(YTDActualprocess - YTDPlanprocess) > YTDPlanprocess * 0.1, "Red", ABS(YTDActualprocess - YTDPlanprocess) < YTDPlanprocess * 0.1, "Amber", IsBudgetOverrun = "Customer" && (Status1 = "Red" || Status2 = "Red"), "Amber", IsBudgetOverrun = "CG" && (Status1 = "Red" || Status2 = "Red"), "Red", OutstandingInvoices = "Yes" && Status1 = "Red", "Red", OutstandingInvoices = "Yes" && Status1 = "Green", "Amber", OutstandingInvoices = "Yes", "Amber", "Green" ) RETURN FinalStatus

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.