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

Thanks Greg, always happy when I see you on a thread!

I am using DAX, and had tried the SWITCH statement.  My challenge was more around all the conditions - I'm getting a "Too many arguments were passed to the AND function.  The maximum argument count for the function is 2.".  I had hoped I had done enough isolation of the IF(AND statements to allow for this to work.  here is my formula...

 

Column = SWITCH (TRUE (), [% Completed] = 1, "100% Completed",
[% Completed] = 0, "Not Started",
(IF(AND([% Completed] < 1, [% Completed] >.66, "Knowledge Stage Completed"))),
(IF(AND([% Completed] > 0, [% Completed] <.661, "Learning Started"))),
[% Completed] = "", "Account Created, Not Started")
 
Any advice on how to fix this so I get my desired outputs?
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