cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AMPAllie
Helper I
Helper I

DAX To Split into Rows on Delimeter

Hello All. So I have an Employee data set.  I needed to get the hierarchal structure of the company so I used the PATH function to achieve this.  but now I have an additional requirement that requires me to parse this column and I can't figure out how to do this.

The result of the PATH function looks like this

 

0175|0116|0423|0066|0001

 

I need to split this column into ROWS based on delimter.  There must be a DAX function to achieve this.

Thanks

 

1 ACCEPTED SOLUTION
Lewis-H
Responsive Resident
Responsive Resident

1) Create a linked table for the Student.

2) Create a table called DummyTbl which is just a list of running numbers from 1 till n. The value of n should be equal to the max number of subjects that a student can have. In this demo, I am assuming that a student can have a max of 10 subjects, so my Dummy table will consist of numbers 1 – 10.

3) Now let us create the DAX queries in DAX Studio. First, we will create a computed column called SubCnt which will give the number of subjects for each row.

EVALUATE
SUMMARIZE (
Student,
[StudentName],
[Subject],
“SubCnt”,
1 + Len ( [Subject] ) – Len ( Substitute ( [Subject], “,”, “” ) )
)

4) The only way we can increase the number of rows of a table is by using the function CrossJoin. So let us CrossJoin the Student table with the DummyTbl.

EVALUATE
Crossjoin (
SUMMARIZE (
Student,
[StudentName],
[Subject],
“SubCnt”,
1 + Len ( [Subject] ) – Len ( Substitute ( [Subject], “,”, “” ) )
),
DummyTbl
)

5) Now filter the resultant table where SubCnt is less than or equal to Dummy column. Now we have got the exact number of rows needed for the end result.

EVALUATE
Filter (
Crossjoin (
SUMMARIZE (
Student,
[StudentName],
[Subject],
“SubCnt”,
1 + Len ( [Subject] ) – Len ( Substitute ( [Subject], “,”, “” ) )
),
DummyTbl
),
DummyTbl[Dummy] <= [SubCnt]
)

6) The only thing left is to split the delimited subjects to the single subject for each row. And the only way to split delimited values is to use the PATHITEM function. To use the PATHITEM function, we should substitute the commas (,) with the pipe (|) symbol.

EVALUATE
ADDCOLUMNS (
FILTER (
CROSSJOIN (
SUMMARIZE (
Student,
[StudentName],
[Subject],
“SubCnt”,
1 + Len ( [Subject] ) – Len ( Substitute ( [Subject], “,”, “” ) )
),
DummyTbl
),
DummyTbl[Dummy] <= [SubCnt]
),
“SubName”,
PATHITEM (
SUBSTITUTE ( Student[Subject], “,”, “|” ),
DummyTbl[Dummy]
)
)

7) Now all we need to do is to select the two required columns.

EVALUATE
SUMMARIZE (
ADDCOLUMNS (
FILTER (
CROSSJOIN (
SUMMARIZE (
Student,
[StudentName],
[Subject],
“SubCnt”,
1 + Len ( [Subject] ) – Len ( Substitute ( [Subject], “,”, “” ) )
),
DummyTbl
),
DummyTbl[Dummy] <= [SubCnt]
),
“SubName”,
pathitem (
Substitute ( Student[Subject], “,”, “|” ),
DummyTbl[Dummy]
)
),
[StudentName],
[SubName]
)
ORDER BY [StudentName]

View solution in original post

11 REPLIES 11
Lewis-H
Responsive Resident
Responsive Resident

1) Create a linked table for the Student.

2) Create a table called DummyTbl which is just a list of running numbers from 1 till n. The value of n should be equal to the max number of subjects that a student can have. In this demo, I am assuming that a student can have a max of 10 subjects, so my Dummy table will consist of numbers 1 – 10.

3) Now let us create the DAX queries in DAX Studio. First, we will create a computed column called SubCnt which will give the number of subjects for each row.

EVALUATE
SUMMARIZE (
Student,
[StudentName],
[Subject],
“SubCnt”,
1 + Len ( [Subject] ) – Len ( Substitute ( [Subject], “,”, “” ) )
)

4) The only way we can increase the number of rows of a table is by using the function CrossJoin. So let us CrossJoin the Student table with the DummyTbl.

EVALUATE
Crossjoin (
SUMMARIZE (
Student,
[StudentName],
[Subject],
“SubCnt”,
1 + Len ( [Subject] ) – Len ( Substitute ( [Subject], “,”, “” ) )
),
DummyTbl
)

5) Now filter the resultant table where SubCnt is less than or equal to Dummy column. Now we have got the exact number of rows needed for the end result.

EVALUATE
Filter (
Crossjoin (
SUMMARIZE (
Student,
[StudentName],
[Subject],
“SubCnt”,
1 + Len ( [Subject] ) – Len ( Substitute ( [Subject], “,”, “” ) )
),
DummyTbl
),
DummyTbl[Dummy] <= [SubCnt]
)

6) The only thing left is to split the delimited subjects to the single subject for each row. And the only way to split delimited values is to use the PATHITEM function. To use the PATHITEM function, we should substitute the commas (,) with the pipe (|) symbol.

EVALUATE
ADDCOLUMNS (
FILTER (
CROSSJOIN (
SUMMARIZE (
Student,
[StudentName],
[Subject],
“SubCnt”,
1 + Len ( [Subject] ) – Len ( Substitute ( [Subject], “,”, “” ) )
),
DummyTbl
),
DummyTbl[Dummy] <= [SubCnt]
),
“SubName”,
PATHITEM (
SUBSTITUTE ( Student[Subject], “,”, “|” ),
DummyTbl[Dummy]
)
)

7) Now all we need to do is to select the two required columns.

EVALUATE
SUMMARIZE (
ADDCOLUMNS (
FILTER (
CROSSJOIN (
SUMMARIZE (
Student,
[StudentName],
[Subject],
“SubCnt”,
1 + Len ( [Subject] ) – Len ( Substitute ( [Subject], “,”, “” ) )
),
DummyTbl
),
DummyTbl[Dummy] <= [SubCnt]
),
“SubName”,
pathitem (
Substitute ( Student[Subject], “,”, “|” ),
DummyTbl[Dummy]
)
),
[StudentName],
[SubName]
)
ORDER BY [StudentName]

@Lewis-H  Sir you are the new DAX God! 🙂 This worked like a charm for my case. I had created a comma separated "Data discrepency Alerts" using Calculated column and the Alerts then needed to be split them into Rows! Your solution is all I had to look at. THANK YOU SO MUCH! This should be documented, blogged!

sajal24
Microsoft
Microsoft

Did you get solution Please share if you can?

I have not found a solution

ChrisMendoza
Super User
Super User

@AMPAllie -

 

Are you trying to obtain end results similar to https://www.daxpatterns.com/parent-child-hierarchies/#organizational-structure?

 

I do not believe the intention of PATH is phyically alter the table but rather allow you to access levels within the path with PATHITEM( ).






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Sort of...i already achieved those particular results.  I am trying to get to the next level. Each employee is on multiple "Teams" as denoted by the Path. I need to be able to list those teams out in rows by employee.

Please provide sample.






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



I can't provide a sample. Apologies. Against company policy.  though all i need to know is if there is a way to split the values in a column into rows based on delimiter. Using DAX. The values are separated by a pipe.

jmalone
Resolver III
Resolver III

This sounds like something you want to do within the Query Editor. You can split a column by delimiter (Transform ribbon > Split Column > by Delimiter)

 

In the window that pops up, you can change the delimiter to Custom (and enter " | " ), and in the Advanced Options, choose "Split into Rows" as shown in the attached image.

 

Split Column by Delimiter.png

 

I have successfully used a DAX expression from Chris Webb - its a bit "complicated" but performance is quite good:
A New Approach To Handling SSRS Multi-Valued Parameters in DAX Queries 

EVALUATE
VAR OrderList = "SO43713|SO43758|SO43784|SO43821"
VAR OrderCount =
    PATHLENGTH ( OrderList )
VAR NumberTable =
    GENERATESERIES ( 1, OrderCount, 1 )
VAR OrderTable =
    GENERATE (
        NumberTable,
        VAR CurrentKey = [Value]
        RETURN
            ROW ( "Key", PATHITEM ( OrderList, CurrentKey ) )
    )
VAR GetKeyColumn =
    SELECTCOLUMNS ( OrderTable, "Key", [Key] )
VAR FilterTable =
    TREATAS ( GetKeyColumn, FactInternetSales[SalesOrderNumber] )
RETURN
    CALCULATETABLE ( FactInternetSales, FilterTable )

I cannot use Power Query as the column I need to parse is a calculated column. 

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors