Dax Query to split the strings using delimiters and expand it in rows in a column measure

Hi Anonymous ,
There are two ways I have tried this.
One is to make a linked table and then cross join it but the way with crossjoin could be potentially way bigger than the table you want to end up with, so heres an alternative that gives a table that's exactly the right size that you want to end up with.

Split = 
VAR ToPaths =
    ADDCOLUMNS (
        SELECTCOLUMNS (
            Table,
            "@ID", Table[Name],
            "@Path", SUBSTITUTE ( Table[Accessories], " ", "," )
        ),
        "@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] )
            ),
            "Name", MAXX ( FILTER ( T, [@Cumulative] = [Cumulative] ), [@ID] ),
            "Accessories", MAXX ( FILTER ( T, [@Cumulative] = [Cumulative] ), [@Path] ),
            "Accessories Number", 1 + [Cumulative] - [Value]
        ),
        "Accessories Split", PATHITEM ( [Accessories], [Name] )
    )

Just replace Table with your table name and the respective columns if you face any errors.

If your requirement is solved, please make THIS ANSWER as SOLUTION and help other users find the solution quickly. Please hit the LIKE button if this comment helps you.😊