📄Using DAX to split columns and create parent categories

We often want to extract text from a column before a specific string.

For example, in the following data, some of the products have their product codes along with them.



We want to analyze this data without the product codes, aggregating data for products with multiple models. For example, for desktop computers, we want the sales value to be 17k (5000+45000+4000+3500). To do this, we need to remove the product model numbers from the product names. All model numbers are separated by “-“. We can take the string before “-“. However both find and search formulas will return error, as not all product names have “-” in them, such as keyboard, mouse, router etc.

Here we have used Fnd = find(“-“,Products[Product]) and Srh = Search(“-“,Products[Product]) and both resulted in an error, because not all the products in product column contain “-“.



The solution to the problem is quite simple. We need to provide a value in the find or search formula, if the character we are searching for is not found. Following formulas will return “0”, if the “-” is not found in the product column.
Fnd = find(“-“,Products[Product],,0)
Srh = Search(“-“,Products[Product],,0)

However, we will use the len(Products[Product]) instead of “0” because we want to use this formula with left() formula. In that case either the number of character where “-” is found, is returned, or the length of the string is returned. Both formulas will generate the same result, therefore we will keep one of them.



To properly use this formula with the left() formula, we need to make a small adjustment. The final formula looks like this.
Fnd = find(“-“,Products[Product],,len(Products[Product])+2)-2

If we do not remove two from the find(), the left() formula will end up getting “Desktop Computer – ” instead of “Desktop Computer”. And if we do not add 2 to the len() formula, the left() formula will get “Mou” instead of “Mouse”.

Here is the final output.




Dataset: https://docs.google.com/spreadsheets/d/1Nr__pemMdWI5L7WxiGm1i-o2ZlA9o_kK7Ll8-X8pR3s/edit?usp=sharing

Power BI file:
https://drive.google.com/file/d/1t74XnNUvk21fjawMfCzPoV0Mypao6Bjn/view?usp=sharing

Relevant Video Tutorial:
https://youtu.be/AuIzhIxGlXg