Conditional Custom Column with multiple cases

[Anonymous]

I am wanting to create a custom column from conditions:

Here is my custom Column formula, but it has errors; please help!

If there is a better way of doing this, please let me know.

Thanks!

The PowerBI Queen 🙂

[Vvelarde]

@Anonymous

Hi don't use the parentesis (

Regards

Victor

[Anonymous]

That still did not work. It produced a column with all errors. 

Is there a way I can add a column using dax by using the same logic?

[Sean]

@Anonymous

What data type is [ClientName] ?

Also what happens if you change <= 5000 to only < 5000 in the Query Editor

As far as DAX try this...

LocType (DAX Column) =
IF (
    ISBLANK ( 'TableName'[ClientName] ),
    "Null",
    IF (
        'TableName'[ClientName] = 0,
        "Default",
        IF ( 'TableName'[ClientName] < 5000, "Branch", "Fran" )
    )
)

Hope this works and helps!

[Anonymous]

Thanks! 

The column is numeric with the exception of 2 fields: Alternate and American

These fields should be listed as "Other" in the new column. 

I will try this and let you know the result.

[Sean · Accepted solution]

@Anonymous

Okay that explains why you are getting the error and makes it a bit more complicated...

So the column [clientName] will be treated as text even though there are only 2 fields that are text

Give this DAX column formula a try...

Basically we check if the value in a cell can be converted to a number and then decide which condition it falls in

LocType (DAX Column) = 
IF (
    ISERROR ( VALUE ( 'Table'[ClientName] ) + 1 ),
    SWITCH (
        FIRSTNONBLANK ( 'Table'[ClientName], 1 ),
        "", "Null",
        "Alternate", "Other",
        "American", "Other"
    ),
    SWITCH (
        TRUE (),
        VALUE ( 'Table'[ClientName] ) = 0, "Default",
        VALUE ( 'Table'[ClientName] ) < 5000, "Branch",
        VALUE ( 'Table'[ClientName] ) >= 5000, "Fran"
    )
)

Hope this works and helps!

I tested on a small sample I created and it seems to work here...

Good Luck!

[Anonymous]

I received this error after creating the formula. 

Any help?

[Sean]

This error indicates you are creating a Measure instead of a column.

Make sure you are creating a column 

[Anonymous]

Thanks! I updated it to create a column, but it's still giving me this error:

[Sean]

@Anonymous

If you are creating a NEW COLUMN - the formula would be evaluated on each row and you would not need an aggregator

as the error message advises because on each row there's only 1 value

[Anonymous]

Thanks! I got it to work perfectly 🙂 You're amazing!

[Anonymous]

Thanks! 

You are very helpful!!