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'...
function
Switch()
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.