Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear all
I have a table in Power BI where it contains a created with DAX that displays the information separated by spaces.
E.g: #DOUBLE #REOPEN #MISSINGINFORMATION
Case Number | TAGS |
A-123456789 | #DOUBLE #REOPEN #MISSINGINFORMATION |
What I want to create, is a new column that will split this information into multiple rows.
Like in this example:
Case Number | TAGS Split |
A-123456789 | #DOUBLE |
A-123456789 | #REOPEN |
A-123456789 | #MISSINGINFORMATION |
However, this new column has to be done in DAX.
Would you please assist me?
Regards
Solved! Go to Solution.
That looks like it works but I wasn't happy with the way they generate a crossjoin that could be potentially way bigger than the table you want to end up with, so I've written an alternative that gives a table that's exactly the right size that you want to end up with.
Split =
VAR ToPaths =
ADDCOLUMNS (
SELECTCOLUMNS (
CaseTags,
"@ID", CaseTags[Case Number],
"@Path", SUBSTITUTE ( CaseTags[TAGS], " ", "|" )
),
"@Length", PATHLENGTH ( [@Path] )
)
VAR T =
ADDCOLUMNS (
ToPaths,
"@Cumulative", SUMX ( FILTER ( ToPaths, [@ID] <= EARLIER ( [@ID] ) ), [@Length] )
)
RETURN
ADDCOLUMNS (
SELECTCOLUMNS (
ADDCOLUMNS (
GENERATESERIES ( 1, SUMX ( T, [@Length] ) ),
"Cumulative", MINX ( FILTER ( T, [@Cumulative] >= [Value] ), [@Cumulative] )
),
"Case Number", MAXX ( FILTER ( T, [@Cumulative] = [Cumulative] ), [@ID] ),
"TAGS", MAXX ( FILTER ( T, [@Cumulative] = [Cumulative] ), [@Path] ),
"Tag Number", 1 + [Cumulative] - [Value]
),
"TAGS Split", PATHITEM ( [TAGS], [Tag Number] )
)
Hi, @datanau001
Refer:
http://sqljason.com/2013/06/split-delimited-row-into-multiple-rows.html
I tried to solve this problem in the same way, based on the blog I referenced
Create a table called Max Split which is just a list of running numbers from 1 till n.
The value of n should be equal to the max number of TAGS that Case Number can have
Sample:
Calculate Table:
_SubTable =
SUMMARIZE (
ADDCOLUMNS (
FILTER (
CROSSJOIN (
SUMMARIZE (
'Table',
'Table'[Case Number],
'Table'[TAGS],
"Number of separator",
1 + LEN ( 'Table'[TAGS] )
- LEN ( SUBSTITUTE ( 'Table'[TAGS], "#", "" ) )
),
'Max Split'
),
'Max Split'[Amount Split] <= [Number of separator]
),
"SubName", PATHITEM ( SUBSTITUTE ( 'Table'[TAGS], "#", "|" ), 'Max Split'[Amount Split] )
),
'Table'[Case Number],
[SubName]
)
It's worth noting that I deleted the first delimiter, otherwise a blank line will be generated.
Please refer to the attachment below for details
Is this the result you want? Hope this is useful to you
Please feel free to let me know If you have further questions
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @datanau001
Refer:
http://sqljason.com/2013/06/split-delimited-row-into-multiple-rows.html
I tried to solve this problem in the same way, based on the blog I referenced
Create a table called Max Split which is just a list of running numbers from 1 till n.
The value of n should be equal to the max number of TAGS that Case Number can have
Sample:
Calculate Table:
_SubTable =
SUMMARIZE (
ADDCOLUMNS (
FILTER (
CROSSJOIN (
SUMMARIZE (
'Table',
'Table'[Case Number],
'Table'[TAGS],
"Number of separator",
1 + LEN ( 'Table'[TAGS] )
- LEN ( SUBSTITUTE ( 'Table'[TAGS], "#", "" ) )
),
'Max Split'
),
'Max Split'[Amount Split] <= [Number of separator]
),
"SubName", PATHITEM ( SUBSTITUTE ( 'Table'[TAGS], "#", "|" ), 'Max Split'[Amount Split] )
),
'Table'[Case Number],
[SubName]
)
It's worth noting that I deleted the first delimiter, otherwise a blank line will be generated.
Please refer to the attachment below for details
Is this the result you want? Hope this is useful to you
Please feel free to let me know If you have further questions
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey @datanau001 ,
that is possible. Check the following article, they describe exactly your scenario:
Split a Delimited Row into Multiple Rows using DAX Queries – Some Random Thoughts (sqljason.com)
That looks like it works but I wasn't happy with the way they generate a crossjoin that could be potentially way bigger than the table you want to end up with, so I've written an alternative that gives a table that's exactly the right size that you want to end up with.
Split =
VAR ToPaths =
ADDCOLUMNS (
SELECTCOLUMNS (
CaseTags,
"@ID", CaseTags[Case Number],
"@Path", SUBSTITUTE ( CaseTags[TAGS], " ", "|" )
),
"@Length", PATHLENGTH ( [@Path] )
)
VAR T =
ADDCOLUMNS (
ToPaths,
"@Cumulative", SUMX ( FILTER ( ToPaths, [@ID] <= EARLIER ( [@ID] ) ), [@Length] )
)
RETURN
ADDCOLUMNS (
SELECTCOLUMNS (
ADDCOLUMNS (
GENERATESERIES ( 1, SUMX ( T, [@Length] ) ),
"Cumulative", MINX ( FILTER ( T, [@Cumulative] >= [Value] ), [@Cumulative] )
),
"Case Number", MAXX ( FILTER ( T, [@Cumulative] = [Cumulative] ), [@ID] ),
"TAGS", MAXX ( FILTER ( T, [@Cumulative] = [Cumulative] ), [@Path] ),
"Tag Number", 1 + [Cumulative] - [Value]
),
"TAGS Split", PATHITEM ( [TAGS], [Tag Number] )
)
Hello @AlexisOlson,
I tried with the above code but I'm getting the message:
The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value
See below:
It is code for a new calculated table.
It clearly cannot work as a calculated column because the existing table does not have enough rows.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
67 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |