Text.Split using DAX

This post was inspired by one of my solutions in the Community. Click here to check the question and answer

We have this Dimension Table.

Category	Product
Fruits	Apple, Banana, Fig, Mango, Grapes, Oranges, Lychee
Vegetables	Lettuce, Onion, Garlic, Potatoes, Okra, Spinach, Tomato

Power Query provides us with Text.Split function to split above products into separate rows. Once transformed this Table can then be connected to FACT Table using “Products” column. So our desired Table is

Category	Products
Fruits	Apple
Fruits	Banana
Fruits	Fig
Fruits	Mango
Fruits	Grapes
Fruits	Oranges
Fruits	Lychee
Vegetables	Lettuce
Vegetables	Onion
Vegetables	Garlic
Vegetables	Potatoes
Vegetables	Okra
Vegetables	Spinach
Vegetables	Tomato

What if for some reason we can not use Power Query (lets say the first table is a CALCULATED TABLE)

DAX does not have a function similar to TEXT.SPLIT but we can combine few DAX functions to achieve the same results

New Table =
VAR myvalues =
    ADDCOLUMNS ( Table1, "ProductPaths", SUBSTITUTE ( [Product], ",", "|" ) )
RETURN
    SELECTCOLUMNS (
        GENERATE (
            myvalues,
            ADDCOLUMNS (
                GENERATESERIES ( 1, PATHLENGTH ( [ProductPaths] ) ),
                "MyProducts", PATHITEM ( [ProductPaths], [Value], TEXT )
            )
        ),
        "Category", [Category],
        "Products", [MyProducts]
    )

Here is a bit of explanation for above formula

1. First we substitute the existing delimiter “,” with ” |” so that we can make use of PATH functions
2. PATHLENGTH gives us total number of products in each category
3. We then use GENERATESERIES in conjunction with PATHITEM to create a Table of Products for each category
4. We then use GENERATE to cross join it with the original table
   

Click here to download pbix file