02-01-2018 10:19 AM - last edited 06-27-2018 13:45 PM
Sometimes you need to know how many items are in a list of text. As long as they are separated by some common delimiter you can use this trick:
CountOfItems = LEN(MAX(List[List])) - LEN(SUBSTITUTE(MAX(List[List]),",","")) + 1
This proposed quick measure would take as input, the column as well as the delimiter. Generacized version below:
CountOfItems = LEN(MAX(<Table[Column]>)) - LEN(SUBSTITUTE(MAX(<Table[Column]>),"<Delimiter>","")) + 1
Question for you, lets assume that you want count all of the occurances for each item in the list column. Essentially having 3 Columns:
1. The list column
2. Count of the number of items in each cell
3. Count of the number of occurrances for each item -> Ex: One = 3 / Two = 2 / Three = 1
How would you go about that?
Well, the way that I would go about that would be to split out my string in Power Query and unpivot. But, if you want a DAX solution with the above format data I will try to create one. I believe there is a path to success using the PATH set of functions but anything in DAX is probably going to end up pretty complex.
I am trying your suggestion to split the string in power query and unpivot. My problem now is that i am essentially making my data set incredibly large now with additional rows and duplicates.
Please see screenshot below.
Pretty certain that I am doing this wrong but I am certainly learning. Am I on the right track? There is no way to create another column where I am not duplicating rows and will yield a count of the unique items?
FYI...I appreciate you so much for the guidance!
Yeah, I was thinking through this and it really becomes difficult to do that level of string parsing in DAX. There just aren't really the functions that you would need for it. You would almost need a SPLIT function or a reverse CONCATENATEX basically where you would return the values in a single column table that you could then do a DISTINCT on and then use that to count up how many of each DISTINCT value you have. But there is no SPLIT function (hint, hint Power BI Team).
So, since you don't have a SPLIT function, then you would have to do some sort of SEARCH for a comma. Record the position of the comma. Get all characters before comma, trim whitespace and then do a SEARCH for the word, another SEARCH from the position of the end of that word until you run out of the length of the original phrase. That's not realistically going to happen because DAX is really against any attempted recursive calculation. It's simply not reasonable.
But, I did think about it. 🙂