QuickTip – Split a delimited text into a Table in DAX

Today I was challenged with a problem and did not find an easy solution to it online, so I decided to write this quick post on it.

The challenge was to create a measure in Table1 that would sum on Table2 all the Rows whose first character was a match with any of the categories on a text separated by “|”:

2018-08-27_19-30-51

The problem is that the “Categories” column is a text separated by “|” and there’s no built in DAX function to convert a delimited text into a table.

The solution is to use PATH + GENERATESERIES + PATHITEM + GENERATE:

2018-08-27_19-33-49.png

 

Hope it helps.

Advertisements