Need a semi complex if / Else statement

[Anonymous]

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"

[jthomson]

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

[Greg_Deckler]

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

[Anonymous]

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?

[v-shex-msft]

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]

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.

[v-shex-msft]

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]

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

[v-shex-msft]

Hi @Anonymous,

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

Regards,
Xiaoxin Sheng

[Anonymous]

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"
        )
    )
)

I'm now seeing a Function 'SWITCH' does not support comparing values of type Number with values of type Text error. Consider using the VALUE or FORMAT function to convert one of the values.  I tried formatting the column (which was set to Decimal Number and General) to Decimal Number and Decimal Number - didn't help.  

[Anonymous]

Doh.  Forrest for the trees....