RETURN multiple VAR results in DAX

[Anonymous]

Hi Experts,

I want to return 3 to 4 VAR in a single DAX calculated column based on the each VAR conditions. 

Below the formula i have used to apply.

Cost Status =
VAR CostSpentYTD = [Cost Spent YTD]
VAR PlannedCostYTD = [Planned cost YTD]
VAR OverallBudgetedCost = [Overall budgeted cost]
VAR NeededToComplete = [What is still needed to complete the project?]
VAR spentandpending = [Cost Spent YTD]+[What is still needed to complete the project?]
VAR budgetdeviation = OverallBudgetedCost - spentandpending
VAR CustomerDependency = [Is the budget overrun due to customer dependency/CG side?]
VAR InitialStatus =
SWITCH(
TRUE(),
ABS(CostSpentYTD-PlannedCostYTD) > PlannedCostYTD * 0.1, "Red",
ABS(CostSpentYTD-PlannedCostYTD) < PlannedCostYTD * 0.1, "Amber",
ABS(CostSpentYTD-PlannedCostYTD) = PlannedCostYTD * 0.1, "Green"
)
VAR BudgetStatus =
SWITCH(
TRUE(),
ABS(budgetdeviation) > OverallBudgetedCost*0.1,"Red",
ABS(budgetdeviation) < 0 && budgetdeviation > OverallBudgetedCost*0.1,"Amber",
"Green"
)
VAR CombinedStatus =
SWITCH(
TRUE(),
CustomerDependency = "Customer" && (InitialStatus = "Red" || BudgetStatus = "Red"), "Amber",
CustomerDependency = "CG" && (InitialStatus = "Red" || BudgetStatus = "Red"), "Red","Green"
)
RETURN
SWITCH(TRUE(),
InitialStatus,BudgetStatus,CombinedStatus
)

The above DAX is returing error .. the below table is the output , now i have returned only Initialstatus VAR , so that 1st 2 rows are giving exact results and rest all 4 rows are just applied the RED based on the Otherwise "Red" condition

Expected result is : order from top
Amber , Green , Red , Amber , Amber , Green 

I have all the above scenarios in each account names in the above sample table , so whenever the above SWITCH conditions are matched then all the 3 created VAR should apply the logics and produced the results. At a single time cannot able to showcase all Variables.
Few more conditions are yet to develop but please so far help me to showcase above 3 VARs in a DAX which i created.

Thanks

DK

[lbendlin]

I want to return 3 to 4 VAR in a single DAX calculated column

Only if you create a text concatenation of these values.

[Anonymous]

@lbendlin  yes but in the table , all the accounts has a matched conditions , so if we use text concat then it will be something based on the results right ?
Can you please help me to suggest idea based on my conditions ?

[Rupak_bi]

Hi @Anonymous 
try below DAX
Cost Status =
VAR CostSpentYTD = [Cost Spent YTD]
VAR PlannedCostYTD = [Planned cost YTD]
VAR OverallBudgetedCost = [Overall budgeted cost]
VAR NeededToComplete = [What is still needed to complete the project?]
VAR spentandpending = [Cost Spent YTD]+[What is still needed to complete the project?]
VAR budgetdeviation = OverallBudgetedCost - spentandpending
VAR CustomerDependency = [Is the budget overrun due to customer dependency/CG side?]
VAR InitialStatus =
SWITCH(
TRUE(),
ABS(CostSpentYTD-PlannedCostYTD) > PlannedCostYTD * 0.1, "Red",
ABS(CostSpentYTD-PlannedCostYTD) < PlannedCostYTD * 0.1, "Amber",
ABS(CostSpentYTD-PlannedCostYTD) = PlannedCostYTD * 0.1, "Green"
)
VAR BudgetStatus =
SWITCH(
TRUE(),
ABS(budgetdeviation) > OverallBudgetedCost*0.1,"Red",
budgetdeviation < 0 && ABS(budgetdeviation )> OverallBudgetedCost*0.1,"Amber",
"Green"
)
VAR CombinedStatus =
SWITCH(
TRUE(),
CustomerDependency = "Customer" && (InitialStatus = "Red" || BudgetStatus = "Red"), "Amber",
CustomerDependency = "CG" && (InitialStatus = "Red" || BudgetStatus = "Red"), "Red","Green"
)
RETURN
SWITCH(TRUE(), 

[Planned cost YTD]<>blank(),InitialStatus,
[Is the budget overrun due to customer dependency/CG side?]<>blank(),CombinedStatus,
BudgetStatus
)
let me know if this works as expected or not.

[lbendlin]

You can only RETURN a single scalar value. There is no other way.

[Rupak_bi]

Hi @Anonymous 

Your  switch function in return does not have any logical operator and that's why it's not switching.