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

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:

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Sean
        Community Champion
        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: