Updating the values of variables in DAX

Question

I need to change the value of variables in a switch in dax:

switch( true(),
    
    condition1,

    var test1 = 2
    var test2 = 3

    ,condition 2,

    var test1 = 4
    var test2 = 5

    ,
    var test1 = 7
    var test2 = 6       
)

I need to do this because I have to change massive number of variables depending on the condition, and I don't want to have a switch for every single variable.

I already tried this approach with switch and it works without variables. It's like dax does not allow you to change the value of a variable after the first time it was assigned.

edit

hello, here is the example of what I want to achieve:

Value Daily Form = 




SWITCH( true(),

// if both are selected
(ISFILTERED(tblAAA[AAA_name]) && ISFILTERED(tblBBB[BBB_name])),


    var Target = sum(F_Daily_AAA_BBB[target])
    var TotalPayments = sum(F_Daily_AAA_BBB[vlr_total_payment])


// if AAA is selected
, (ISFILTERED(tblAAA[AAA_name]) && not(ISFILTERED(tblBBB[BBB_name]))),



    var Target = sum(F_Daily_AAA[target])
    var TotalPayments = sum(F_Daily_AAA[vlr_total_payment])



// if BBB is selected
, (not(ISFILTERED(tblAAA[AAA_name])) && ISFILTERED(tblBBB[BBB_name])),


    var Target = sum(F_Daily_BBB[target])
    var TotalPayments = sum(F_Daily_BBB[vlr_total_payment])


// none is selected
,



    var Target = sum(F_Daily_OR[target])
    var TotalPayments = sum(F_Daily_OR[vlr_total_payment])
    
)


var result =  
    SWITCH(FIRSTNONBLANK(tblKPIs[Group_Name], tblKPIs[Group_Name]),

    "Target (€)", IF(Target > 0, FORMAT(Target,  "€ #,##"), if(SELECTEDVALUE('Calendar'[isWorkingDay]) = 1, "€ 0", BLANK())), 
    "Total Payments (€)", IF(TotalPayments > 0, FORMAT(TotalPayments,  "€ #,##"), IF(SELECTEDVALUE('Calendar'[isWorkingDay]) = 1, "€ 0", BLANK())), 
    
)
return result

in the beginning the code that I have to modify is:

Value Daily Form = 







    var Target = sum(F_Daily_OR[target])
    var TotalPayments = sum(F_Daily_OR[vlr_total_payment])
    



var result =  
    SWITCH(FIRSTNONBLANK(tblKPIs[Group_Name], tblKPIs[Group_Name]),

    "Target (€)", IF(Target > 0, FORMAT(Target,  "€ #,##"), if(SELECTEDVALUE('Calendar'[isWorkingDay]) = 1, "€ 0", BLANK())), 
    "Total Payments (€)", IF(TotalPayments > 0, FORMAT(TotalPayments,  "€ #,##"), IF(SELECTEDVALUE('Calendar'[isWorkingDay]) = 1, "€ 0", BLANK())), 
    
)
return result

The only difference is the instead of 2 variables there are like 75, and I don't want to make a switch for each of them because this will spaghettify the code.

Also I have to modify like 50 of this Metrics full of variables, so I need a quick copy paste solution like that switch, which worked in other cases in which the code didn't have variables.

Thank you for taking the time to help me.

score 2 · Accepted Answer · answered Mar 31 '22 at 18:57

2

It is indeed, because variables in DAX are actually constants. Variables are immutable. You can store the value in variable but you cannot change it later.

Here is a definition of the DAX variable from the documentation:

Stores the result of an expression as a named variable, which can then be passed as an argument to other measure expressions. Once resultant values have been calculated for a variable expression, those values do not change, even if the variable is referenced in another expression.

Find more in the documentation.

What exactly do you want to achieve by this? Creating separate measure for each condition and then another measure with switch condition won't work for you?

answered Mar 31 '22 at 18:57

intruderr

355
1
7

Hello, thank you for the answer! I have to change a spaghetti code where in each metric many metrics are calculated with a switch. If some condition is met i need to take data from a table instead of another, but i wont want to write "if then else" in any single variable that represent the metric. I want to do it in big blocks, because I have to change massive amount of code. – AndreA Mar 31 '22 at 22:10
1

Could you provide some example data and code, please? – intruderr Apr 01 '22 at 05:59
I added more code! – AndreA Apr 01 '22 at 09:16
That cannot be achieved using only one measure. The solution will be more complex, but this is manageable, though. Furthermore, in your current solution, you need to `RETURN` your `VAR` after each code block. You do not do that, hence DAX will return an error. Let me know if you want me to give you the solution handling the issue, but it will require some changes in your current model. We would need some additional measures as well. – intruderr Apr 03 '22 at 18:05
I resolved the problem creating a set of variable for every fact with slightly different names and then I use each sub set of variables in a different part of the switch. Thank you very much for your help! :) – AndreA Apr 04 '22 at 21:18
I am glad you've found the solution! That is one of the method I was thinking about. The other one required to add a consolidated table (all facts appended together) and then create a switch onto that table. – intruderr Apr 04 '22 at 21:35

Updating the values of variables in DAX

1 Answers1