Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Conditional Custom Column with multiple cases

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

Need Help
  • Sean's avatar
    Sean
    8 years ago

    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! :smileyhappy:

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

    Good Luck! :smileyhappy:

11 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    8 years ago

    Anonymous

     

    Hi don't use the parentesis (

     

    Regards

     

    Victor

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Sean
        Community Champion
        8 years ago

        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! :smileyhappy: