Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Multiple condition function in Dax

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

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi  Anonymous ,

        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