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

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 (

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

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

Click here to download pbix file