Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 🙂
Solved! Go to 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
Hi don't use the parentesis (
Regards
Victor
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?
@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!
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.
@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!
I received this error after creating the formula.
Any help?
This error indicates you are creating a Measure instead of a column.
Make sure you are creating a column
Thanks! I updated it to create a column, but it's still giving me this error:
@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
Thanks! I got it to work perfectly 🙂 You're amazing!
Thanks!
You are very helpful!!
User | Count |
---|---|
91 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
99 | |
88 | |
73 | |
60 | |
58 |