topic Re: RETURN multiple VAR results in DAX in DAX Commands and Tips

RETURN multiple VAR results in DAX

Anonymous — Wed, 23 Oct 2024 13:34:50 GMT

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

Re: RETURN multiple VAR results in DAX

Rupak_bi — Wed, 23 Oct 2024 14:12:50 GMT

Hi Anonymous

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

Re: RETURN multiple VAR results in DAX

lbendlin — Wed, 23 Oct 2024 14:16:09 GMT

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

Only if you create a text concatenation of these values.

Re: RETURN multiple VAR results in DAX

Anonymous — Thu, 24 Oct 2024 06:48:56 GMT

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 ?

Re: RETURN multiple VAR results in DAX

lbendlin — Thu, 24 Oct 2024 09:01:24 GMT

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

Re: RETURN multiple VAR results in DAX

Rupak_bi — Thu, 24 Oct 2024 10:35:20 GMT

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.