DAX To Split column into multiple rows

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.