topic Re: Get the characters from a text using DAX in DAX Commands and Tips

Get the characters from a text using DAX

juhoneyighot — Mon, 20 Nov 2023 14:04:10 GMT

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.

Re: Get the characters from a text using DAX

FreemanZ — Tue, 21 Nov 2023 02:07:58 GMT

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:

Re: Get the characters from a text using DAX

Subash_Govind — Tue, 21 Nov 2023 12:37:23 GMT

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...