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.

[Ahmedx]

pls try this

Status = IF(
    NOT ISBLANK( 'Table'[NC.IN] ),
    PATHITEM( SUBSTITUTE( 'Table'[Jobtype], "__", "|" ), 2 ),
    ""
)

[Ahmedx · Accepted solution]

you need creat a measure

[juhoneyighot]

@Ahmedx The solution works for Status Column.

Can I also ask help for the Phase Column. Same condition with Status column but Phase Column is extracted before the 2_(two Underscores).

Thank you.

[juhoneyighot]

this is ok now.. I use the PATHITEMREVERSE Dax.

Thank you.

[juhoneyighot]

@Ahmedx 

I tried it but the error is this.

[DOLEARY85]

Hi,

try these in custom columns:

Phase= If ([NC.IN] = "", "", left([Jobtype], search("__",[Jobtype],,0)))

Status= If ([NC.IN] = "", "", right([Jobtype], len([Jobtype]) - search("__",[Jobtype],,0)))

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[juhoneyighot]

Hi @DOLEARY85 

if i added the Calculated Phase column the error is this.

When I click see details 

[DOLEARY85]

I assume you're using direct query mode which is going to make it difficult with DAX in calculated columns as what you can do is limited, you could try it as a measure but you have to be a little creative:

Phase = If (CALCULATE(max([NC.IN])) = "", "", left(CALCULATE(max([Jobtype])), search("__",CALCULATE(max([Jobtype])),,0)))

If this doesn't work you might be better creating a view in the source with the additional fields and then connecting that view to Power BI.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍