Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
BW40
Helper I
Helper I

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 "unit" but that ignores the second hyphen. Also I do not think SPLIT is a DAX function.

 

column value: "area - unit - supervisor"

I want to generate seperate columns for area, unit, and supervisor.

 

Is there a dax function for splitting columns based on deliminators? It's a basic function in Tableau but trying to translate it into Power BI Dax

4 REPLIES 4
v-tianyich-msft
Community Support
Community Support

Hi @BW40 ,

 

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

vtianyichmsft_0-1700117407374.png

 

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.

 

chonchar
Helper V
Helper V

@BW40 

I have never done it, but I did find this post: 

 

https://community.fabric.microsoft.com/t5/Desktop/split-column-by-delimiter-in-dax/m-p/3129539#M1059...

 

Best of luck,

 

Cam

vanessafvg
Super User
Super User

in power query you can right click on a column and split by delimiter and other options.  It is also better from a performance perspective to create calculated columns in power query. 

 

if you need more help than that, i think you will need to provide a sample of your data (in text form) - it is quite difficult to provide a accurate solution without knowing what the data looks like.

 

https://support.microsoft.com/en-au/office/split-a-column-of-text-power-query-5282d425-6dd0-46ca-95b...





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




I found the simplest solution was to create two split by deliminiter functions in query. That allowed me to break up the fields but it would not allow me to create an IF condition. I had to create groups and do other manual clean up actions to get the data to an approximation of what is needed. It isn't exact but it is functional.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Users online (718)