Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Splitting one column into three columns using DAX

I have a combined field that has area, unit, and supervisor. I can split it in power query but it gets messy as some fields only have a unit name. I'm looking for IF( SEARCH("-") THEN SPLIT, else "un...
Need Help
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous ,

 

Based on your description, I made simple samples and you can check the results as follows:

 

DAX expression:

Area = VAR _CONTAIN = CONTAINSSTRING(MAX('Table'[Text]),"-")
VAR _PATHITEM = SUBSTITUTE(MAX('Table'[Text]),"-","|")
RETURN
IF(_CONTAIN,PATHITEM(_PATHITEM,1),"")

Unit = VAR _CONTAIN = CONTAINSSTRING(MAX('Table'[Text]),"-")
VAR _PATHITEM = SUBSTITUTE(MAX('Table'[Text]),"-","|")
RETURN
IF(_CONTAIN,PATHITEM(_PATHITEM,2),MAX('Table'[Text]))

Supervisor = VAR _CONTAIN = CONTAINSSTRING(MAX('Table'[Text]),"-")
VAR _PATHITEM = SUBSTITUTE(MAX('Table'[Text]),"-","|")
RETURN
IF(_CONTAIN,PATHITEM(_PATHITEM,3),"")

 

An attachment for your reference. Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

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