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

[juhoneyighot]

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.

[Anonymous · Accepted solution]

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

[lbendlin]

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.