Get the characters from a text using DAX

[juhoneyighot]

Hello!

I need help to get the characters from a certain text using DAX. I have this Column "JobType". See table below.

Jobtype	NC.IN	Phase	Status
NC 120_Rough Builder Walk__10Not Started	NC	NC 120_Rough Builder Walk	10Not Started
NC.IN 720_Dehu_Estimate__90Complete	NC.IN	NC.IN 720_Dehu_Estimate	90Complete
IN 622_CO EQ_QC__90Complete	IN	IN 622_CO EQ_QC	90Complete
Install_Estimates

 I have to use a DAX to get the characters for the Phase and Status Column from Jobtype column with this condition. If Column NC.IN is "" then ""

else 

Phase column is extracted before the __(2underscores)

Status column is extracted after the __(2underscores)

I am confused what correct DAX formula to use. 

Thank you.

[Subash_Govind · Accepted solution]

Hi @juhoneyighot ,

Below are DAX codes for creating new column,

Phase =

VAR BEFORE_DELIMITER = LEFT('DAX Custom column'[Job Type],(SEARCH("__",'DAX Custom column'[Job Type]))-1)

RETURN IF('DAX Custom column'[NC.IN] = BLANK()," ",BEFORE_DELIMITER)

Status =

VAR AFTER_DELIMITER = RIGHT('DAX Custom column'[Job Type],(LEN('DAX Custom column'[Job Type])-SEARCH("__",'DAX Custom column'[Job Type]))-1)

RETURN IF('DAX Custom column'[NC.IN] = BLANK(),BLANK(),AFTER_DELIMITER)

It worked as below...

[FreemanZ]

hi @juhoneyighot ,

in cases like this and you can, it makes more sense to do it in Power Query. Try to add a custom column like:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nNWMDQyiA/KL03PUHAqzcxJSS1SCE/MyY6PNzTwyy9RCC5JLCpJTVHSASoFEzjUA+VQNcTqgEzX8/RTMAdqcEnNKI13LS7JzE0sSY2PtzRwzs8tyEktSQWbCVQFozFVA2WQlIOMBaoyMzKKd/ZXcA2MD3RGMw5sFpoSUs2AothYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Jobtype = _t, NC.IN = _t, Phase = _t, Status = _t]),
    #"Added Custom" = Table.AddColumn(
        Source, 
        "Custom", 
        each if [NC.IN]=""
        then ""
        else
        Text.AfterDelimiter( 
            [Jobtype], 
            "_", 
            Text.Length([Jobtype]) - Text.Length( Text.Remove([Jobtype], "_" ))-1
        )
    ),
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "Custom2", each if [NC.IN]=""
        then ""
        else
        Text.BeforeDelimiter(
            [Jobtype],
            "_",
            Text.Length([Jobtype]) - Text.Length( Text.Remove([Jobtype], "_" ))-2
        )
    )
in
    #"Added Custom2"

it worked like: