Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Dax - switch function

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

function
Switch()

3 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    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]
        )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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?

       

      • CNENFRNL's avatar
        CNENFRNL
        Community Champion
        5 years ago

        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.