Multiple condition function in Dax

[jeongkim]

Hi guys,

Can anyone help me how to make new column using measure to lookup the Grade below. 

Type A, the bigger the Green is, and Type B is opposite. 

So how can I run IF or Switch function?

The function I tried seems wrong,,

Thanks in advance. 

Kim

[yingyinr · Accepted solution]

Hi @jeongkim ,

You can create a calculated column as below in the table 'SKT' to get it:

Icon Grade = 
SWITCH (
    TRUE (),
    'SKT'[Type] = 1
        && 'SKT'[Value] < 'SKT'[Red], 1,
    'SKT'[Type] = 1
        && 'SKT'[Value] > 'SKT'[Red]
        && 'SKT'[Value] <= 'SKT'[Light-Red], 2,
    'SKT'[Type] = 1
        && 'SKT'[Value] >= 'SKT'[Light-Green], 4,
    'SKT'[Type] = 1
        && 'SKT'[Value] >= 'SKT'[Green], 5,
    'SKT'[Type] = -1
        && 'SKT'[Value] >= 'SKT'[Red], 1,
    'SKT'[Type] = -1
        && 'SKT'[Value] < 'SKT'[Red]
        && 'SKT'[Value] >= 'SKT'[Light-Red], 2,
    'SKT'[Type] = -1
        && 'SKT'[Value] <= 'SKT'[Light-Green], 4,
    'SKT'[Type] = -1
        && 'SKT'[Value] <= 'SKT'[Green], 5,
    3
)

Best Regards

[jeongkim]

also how can I add another condition? if the Value is (blank) then just leave it blank without grading? 

[yingyinr]

Hi  @jeongkim ,

You can update the formula of calculated column as below to get it:

Icon Grade =
SWITCH (
    TRUE (),
    ISBLANK ( 'SKT'[Value] ), BLANK (),
    'SKT'[Type] = 1
        && 'SKT'[Value] < 'SKT'[Red], 1,
    'SKT'[Type] = 1
        && 'SKT'[Value] > 'SKT'[Red]
        && 'SKT'[Value] <= 'SKT'[Light-Red], 2,
    'SKT'[Type] = 1
        && 'SKT'[Value] >= 'SKT'[Light-Green], 4,
    'SKT'[Type] = 1
        && 'SKT'[Value] >= 'SKT'[Green], 5,
    'SKT'[Type] = -1
        && 'SKT'[Value] >= 'SKT'[Red], 1,
    'SKT'[Type] = -1
        && 'SKT'[Value] < 'SKT'[Red]
        && 'SKT'[Value] >= 'SKT'[Light-Red], 2,
    'SKT'[Type] = -1
        && 'SKT'[Value] <= 'SKT'[Light-Green], 4,
    'SKT'[Type] = -1
        && 'SKT'[Value] <= 'SKT'[Green], 5,
    3
)

Best Regards

[jeongkim]

Thank you so much! It is simpler and now working 🙂