topic Re: How to get the Number of Characters from a Column with a certain condition in DAX Commands and Tips

How to get the Number of Characters from a Column with a certain condition

juhoneyighot — Sat, 04 Nov 2023 12:10:08 GMT

Hello! I am new to PowerBI.

I need to have a calculated column how to get the number of characters from a certain column(JobType_txt). However, there is one condition. Some of the Jobtype_txt dont have a value in # of _ Column. See screenshot below.

Condition is: If # of _ is " " then Phase_=" " else need a calculation for below requirement.

Example: Jobtype_txt: NC 110_Estimates__10NotStarted

The text is divided into

Prefix: NC 110

"_"

Phase_:Estimates_

"_",

Status:10NtotStarted

So, I need to have a calculated column the number of characters from Prefix until "Phase_". So from JobType_text: NC 110_Estimates_.

So the Phase_=17

I have this sample Excel formula for this. See screenshot below.

My problem is I don't know how to convert this to DAX formula.

I hope you can help me on this.

Thank you.

Re: How to get the Number of Characters from a Column with a certain condition

lbendlin — Sun, 05 Nov 2023 13:59:25 GMT

Does it have to be DAX? You can use the PATH functions for that.

It's much easier to do in Power Query.

Please provide sample data that fully covers your issue. Are there two underscores as the second divider?
Please show the expected outcome based on the sample data you provided.

Re: How to get the Number of Characters from a Column with a certain condition

Anonymous — Tue, 07 Nov 2023 07:20:53 GMT

Hi juhoneyighot,

You can try to use following formula to create a calculated column to get the character counts:

charCount = VAR _path = SUBSTITUTE ( 'Table'[Text], "_", "|" ) VAR _suffix = PATHITEM ( _path, PATHLENGTH ( _path ) ) VAR replaced = SUBSTITUTE ( 'Table'[Text], "_" & _suffix, "" ) RETURN LEN ( replaced )

Regards,

Xiaoxin Sheng