Need help with DAX Formula for the number of characters in a text 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.

[v-xinruzhu-msft]

Hi @juhoneyighot 

You can refer to the following measure

Sample data 

Measure:

Count_txt =
VAR a =
    SUBSTITUTE ( SELECTEDVALUE ( 'Table'[_JobType_txt] ), "_", "|" )
RETURN
    IF (
        NOT ( ISBLANK ( MAX ( 'Table'[#of_] ) ) ),
        LEN ( PATHITEM ( a, 1 ) ) + LEN ( PATHITEM ( a, 2 ) ) + 2,
        0
    )

Output

Best Regards!

Yolo Zhu

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

[juhoneyighot]

@v-xinruzhu-msft 

Here is the entered formula but when I added the field in the visual it has Sum of Phase_

Did I did something wrong?

[v-xinruzhu-msft · Accepted solution]

Hi @juhoneyighot 

It seems that you created it as a calculated column, you need to create it as a measure intstead of a calculated column. if you want to create calculated column, you can refer to the following code.

Column = var a=SUBSTITUTE('Table'[_JobType_txt],"_","|")
return IF(NOT(ISBLANK('Table'[#of_])),LEN(PATHITEM(a,1))+LEN(PATHITEM(a,2))+2,0)

Then when you put the field to visual, you need to select 'Don't summarize' 

Best Regards!

Yolo Zhu

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

[Ahmedx]

pls try this

Count TEXT = 
VAR LenText = LEN(MAX('Table'[_JobType_txt]))
 VAR LenPatch = LEN(PATHITEMREVERSE( SUBSTITUTE(MAX('Table'[_JobType_txt]),"_","|"),1))
 RETURN
 IF( MAX('Table'[#of_]),
LenText - LenPatch ,0)

[Ahmedx]

Share some data to work with (in a format the can be pasted in an MS Excel file) and show the expected result.