Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Need a semi complex if / Else statement

I'm trying to create a new column that looks at an existing column called % Completed (if contains numerical values from 0 to 1 and blanks) and then outputs text based off what value is in that colum...
Need Help
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
7 years ago

HI Anonymous,

 

I'd like to recommend you to use if statement to nested switch function instead of combine them in switch function, switch function not suitable to compare with data range.

Column =
IF (
    AND ( [% Completed] > 0, [% Completed] < .661 ),
    "Learning Started",
    IF (
        AND ( [% Completed] > .66, [% Completed] < 1 ),
        "Knowledge Stage Completed",
        SWITCH (
            [% Completed],
            1, "100% Completed",
            0, "Not Started",
            "", "Account Created, Not Started"
        )
    )
)

Regards,

Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thank you for this.  When I entered that formula, I got the following error:

 

Function 'SWITCH' does not support comparing values of type Number with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    HI Anonymous,

     

    I add value function to switch to convert value to number, maybe you can try to use following formula:

    Column =
IF (
    AND ( [% Completed] > 0, [% Completed] < .661 ),
    "Learning Started",
    IF (
        AND ( [% Completed] > .66, [% Completed] < 1 ),
        "Knowledge Stage Completed",
        SWITCH (
            VALUE ( Table[% Completed] ),
            1, "100% Completed",
            0, "Not Started",
            "", "Account Created, Not Started"
        )
    )
)

     

    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you.  Came back with another error - The syntax for 'Table' is incorrect. (DAX(IF ( AND ( [% Completed] > 0, [% Completed] < .661 ), "Learning Started", IF ( AND ( [% Completed] > .66, [% Completed] < 1 ), "Knowledge Stage Completed", SWITCH ( VALUE ( Table [% Completed] ), 1, "100% Completed", 0, "Not Started", "", "Account Created, Not Started" ) )))).

       

      I couldn't find an expression called Table

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Anonymous,

         

        Table means your table name who host the column.(where [% Completed] field exists)

         

        Regards,
        Xiaoxin Sheng