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 column.  I've tried a bunch of formats.  No luck.  Here is the gist...

 

If % Completed = 1, "100% Completed"

elseif % Completed <1 but > .66, "Knowledge Stage Completed"

elseif % Completed <.661 but > 0, "Learning Started"

elseif % Completed = 0, "Not Started"

elseif % Competed = blank/null, "Account created, Not Started"

 

 

Need Help
Tips and Tricks

10 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

    Are you doing this in DAX or M? I'd use a SWITCH statement in DAX, SWITCH(TRUE()...)

    • 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

  • jthomson's avatar
    jthomson
    Solution Sage
    7 years ago
    It'd certainly be easier to code if you trimmed down your logic, you don't need to say something is both <1 and >0.66 if you've already assigned everything that is 1 to 100% completed, similar with the next step, you just need to say >0