cancel
Showing results for 
Search instead for 
Did you mean: 

Count of List Items

Super User
1810 Views
Highlighted
Super User
Super User

Count of List Items

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

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


win_toeknee Member
Member

Re: Count of List Items

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? 

Super User
Super User

Re: Count of List Items

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


win_toeknee Member
Member

Re: Count of List Items

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! 

 

countitems.splitcolumnunpivot.png

Super User
Super User

Re: Count of List Items

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. Smiley Happy


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!