topic Dax - switch function in DAX Commands and Tips

Dax - switch function

Anonymous — Thu, 15 Oct 2020 08:38:01 GMT

hi all , DAX is A new language for me (:

i have column "postion name" & "Actual amount" most of them come directly from the DB and some are calculated

like 'NRI' is calculation of "GRI" + 'LRI'

when i use SWITCH(TRUE() function:

i use this option:

----

return
SWITCH(TRUE(),
MAX('9 - Controling'[POSITIONS_NAME])="NRI" , CALCULATE(sum('9 - Controling'[Actual Amount]),'9 - Controling'[POSITIONS_NAME])="GRI")

----

I received this error message : dax comparison do not support comparing values of type number with value text

thanks,

sinayha

Re: Dax - switch function

CNENFRNL — Thu, 15 Oct 2020 11:47:00 GMT

Hi, Anonymous , let's format the measure first in order to see it clearly

= SWITCH ( TRUE (), MAX ( '9 - Controling'[POSITIONS_NAME] ) = "NRI", CALCULATE ( SUM ( '9 - Controling'[Actual Amount] ), '9 - Controling'[POSITIONS_NAME] ) = "GRI" )

the CALCULATE( SUM (...) ) part returns a number and then compared with "GRI", which results in an error.

btw, I'm afraid this par is an error-prone expression.

CALCULATE ( SUM ( '9 - Controling'[Actual Amount] ), '9 - Controling'[POSITIONS_NAME] )

Re: Dax - switch function

Anonymous — Thu, 15 Oct 2020 12:49:04 GMT

Thanks so much for your response.

I realized what I did wrong (:

NRI & GRI in the same level so when i did

MAX ( '9 - Controling'[POSITIONS_NAME] ) = "NRI",

Because of the filter the result will be 0 for GRI.

So,, i add calc for 'nri'

NRI Actual 2019 =
VAR maxMonthSelection = SELECTEDVALUE('8 - Date'[MonthNameShort])
var maxyear = DATEVALUE(CONCATENATE(CONCATENATE("2019/",'0 - Measure'[check month]),"/01"))
return
SUMX(
FILTER(
'9 - Controling' ,
'9 - Controling'[Period Date] = maxyear &&
'9 - Controling'[SUBGROUPS1_NAME]= "GRI"
)
,'9 - Controling'[Actual Amount])
+ SUMX(
FILTER(
'9 - Controling' ,
'9 - Controling'[Period Date] = maxyear &&
'9 - Controling'[SUBGROUPS1_NAME]= "Rent-free"
)
,'9 - Controling'[Actual Amount]).

But now the display bothers me (: . Is it possible to add a column that is for the calculation to look like this?

Re: Dax - switch function

CNENFRNL — Thu, 15 Oct 2020 13:15:48 GMT

Hi, Anonymous , frankly speaking, I can bearly help troubleshoot such a long DAX expression without any underlying data model. It would be way much easier if you attach a dummy file with some mockup data.