Word Counts In DAX

Kevin Feasel

2018-10-15

DAX

Philip Seamark shows us a way of splitting strings into words in DAX:

Here is a technique you might consider if you need to split text down to individual words.  This could be used to help count, rank or otherwise aggregate the words in some longer text.  The approach detailed here uses spaces as a delimiter and will not be tripped up if multiple spaces are used between words.

There is no SPLIT function in DAX, so this approach uses the MID function to help find words.

The PBIX file used for the blog can be downloaded here.

[Updated 14th Oct, 2018]
A slightly updated version that uses UNICHAR/UNICODE to preserve the case (“A” versus “a”) of each letter can be downloaded here. The reason for this is DAX stores a dictionary of unique values for every column.  It is the first instance of any value that is added to the dictionary and assigned a new ID.  Subsequent values that are considered the same “A” and “a” are considered the same are assigned the same ID.  Using the UNICHAR/UNICODE version helps preserve the original case of each letter.

It’s an interesting approach and reminded me a bit of using a tally table to split strings in T-SQL.

Related Posts

Hiding Future Dates In DAX Measures

Marco Russo shows how we can define year-to-date measures which don’t include values for incomplete months: The rows between September 2009 and December 2009 should not be visible. The goal here is to display a blank value in these out-of-range, “future” months.A similar issue exists for the year-over-year calculation (YOY). Even though the measure tries […]

Read More

Common DAX Error Messages

Marco Russo takes us through some of the more common Power BI error messages around writing DAX: The message should help the author fix the code, but sometimes the text suggests a possible action without describing the underlying issue. The goal of this article is to explain the more common DAX error messages by providing […]

Read More

Categories

October 2018
MTWTFSS
« Sep Nov »
1234567
891011121314
15161718192021
22232425262728
293031