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.

CategoryProduct
FruitsApple, Banana, Fig, Mango, Grapes, Oranges, Lychee
VegetablesLettuce, 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

CategoryProducts
FruitsApple
Fruits Banana
Fruits Fig
Fruits Mango
Fruits Grapes
Fruits Oranges
Fruits Lychee
VegetablesLettuce
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