Custom Column - Logic query?

[Anonymous]

Hi all,

I am looking to create a new column to show the status of various other columns condition.
 We will have three annswers in this column 
COMPLETE (will be when there is a name in the technician field PLUS a date in the completed field.)
ASSIGNED (will be when there is a name in the technician field PLUS NO date in the completed field.)
LOST ( WIll be when the word Lost appears on the Notes field)

Is this possible for one custom column?

[amitchandak]

@Anonymous , Create a new column in DAX like

New Column =
Switch(True(),
not(isblank([technician])) && not(isblank([completed])) , "COMPLETE",
not(isblank([technician])) && isblank([completed]) , "ASSIGNED",
search("Lost",[Notes],,0) >0 ,"LOST"
)

[mhossain · Accepted solution]

@Anonymous 

Very much possible, try below logic:

if [technician] <> null and [completed] <> null then "COMPLETE"
else
if [technician] <> null and [completed] = null then "ASSIGNED"
else
if Text.Contains("Lost", [Notes]) then "Lost"
else
""

Change the field names and your criteria names accordingly.

[Anonymous]

So close mhossain,
But if the Notes field is null, I get this error
Expression.Error: We cannot convert the value null to type Text.
Details:
Value=
Type=[Type]

[V-lianl-msft]

Hi @Anonymous ,

It can be operated by GUI and input a space.

If the problem persists,Please take a screenshot to show the columns you described.

Please remove any sensitive data before uploading.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mhossain]

@Noblelox

Glad to know that.

On the error with Notes field "Expression.Error: We cannot convert null value to Text type.", try below

Again multiple methods to solve this, in your case for simplicity, before creating this custom column

perhaps you can try to replace these null value in the Notes field by replace method to some text value before your operation.

And then Notes field will not give this error in the custom column.

Hope above makese sense.