cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

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!

custom.PNG

 

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

 

Thanks!

 

The PowerBI Queen 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
Sean Super Contributor
Super Contributor

Re: Conditional Custom Column with multiple cases

@ThePowerBIQueen

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! Smiley Happy

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

DAX - VALUE and SWITCH.png

Good Luck! Smiley Happy

View solution in original post

11 REPLIES 11
Vvelarde Super Contributor
Super Contributor

Re: Conditional Custom Column with multiple cases

@ThePowerBIQueen

 

Hi don't use the parentesis (

 

Regards

 

Victor




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Re: Conditional Custom Column with multiple cases

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 Super Contributor
Super Contributor

Re: Conditional Custom Column with multiple cases

@ThePowerBIQueen

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! Smiley Happy

 

Re: Conditional Custom Column with multiple cases

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 Super Contributor
Super Contributor

Re: Conditional Custom Column with multiple cases

@ThePowerBIQueen

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! Smiley Happy

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

DAX - VALUE and SWITCH.png

Good Luck! Smiley Happy

View solution in original post

Re: Conditional Custom Column with multiple cases

ClientName.PNG

 

I received this error after creating the formula. 

 

Any help?

Sean Super Contributor
Super Contributor

Re: Conditional Custom Column with multiple cases

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

Make sure you are creating a column Smiley Happy

 

 

Re: Conditional Custom Column with multiple cases

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

 

ClientName.PNG

Sean Super Contributor
Super Contributor

Re: Conditional Custom Column with multiple cases

@ThePowerBIQueen

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

Conditional COLUMN.gif

 

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 378 members 3,984 guests
Please welcome our newest community members: