DAX To Split column into multiple rows

[datanau001]

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

[AlexisOlson · Accepted 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] )
    )

[v-angzheng-msft · Accepted solution]

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.

[selimovd]

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)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[AlexisOlson · Accepted 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] )
    )

[datanau001]

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:

[AlexisOlson]

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.