Forum Discussion
DAX To Split into Rows on Delimeter
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]
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.
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 )