Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Sean
Community Champion
Community Champion

@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! 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
Community Champion
Community Champion

@Anonymous

 

Hi don't use the parentesis (

 

Regards

 

Victor




Lima - Peru
Anonymous
Not applicable

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
Community Champion
Community Champion

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

 

Anonymous
Not applicable

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
Community Champion
Community Champion

@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! 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

Anonymous
Not applicable

ClientName.PNG

 

I received this error after creating the formula. 

 

Any help?

Sean
Community Champion
Community Champion

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

Make sure you are creating a column Smiley Happy

 

 

Anonymous
Not applicable

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

 

ClientName.PNG

Sean
Community Champion
Community Champion

@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

Conditional COLUMN.gif

 

Anonymous
Not applicable

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

Anonymous
Not applicable

Thanks! 

 

You are very helpful!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.