Calculate a number of occurrences of unique items within column

[my_username]

I am new to Power BI and I need help on how to measure number of occurences of items within column.

Here is an example:

I have columns c1 (text), and c2 (text). In c2, items are separated by comma.

I need to calculate the number of unique items in c2 for chosen c1, for example:

- if {1} is selected for c1, I will have 3 unique items in c2: {a,b,c}

- if {2,3} is selected for c1, I will have 4 unique items in c2: {a,b,c,d}

and so on.

Any help is appreciated!

[rsbin]

@my_username ,

This should work for you:

Item_Count = IF(LEN(TRIM([c2]))=0,0,LEN([c2])-LEN(SUBSTITUTE([c2],",",""))+1)

c1c2Item_Count

1	a,b,c	3
2	b,c	2
3	a,c,d	3

Used the link below.  If you need to modify, this should be a good resource.

https://learn.microsoft.com/en-us/office/troubleshoot/excel/formulas-to-count-occurrences-in-excel

Although it says "excel", a little modification and it works in DAX.  The text functions are the same.

Trust this works for you.

Regards,

[my_username]

Thank you for your responce. I don't think that this is exactly what I need.

Because if I want to calculate values for c1 selected as {1,3}, then the count of unique items would be 4, because  cumulutive c2 would be {a,b,c,a,c,d} -> filter out for unique -> {a,b,c,d} -> 4 items. This is my main challenge.

[rsbin · Accepted solution]

Thanks for the clarification.  Apologies for the mis-interpretation.

The easiest way to handle this is to use Power Query to split your column [c2] into rows.

Then use DistinctCount in Dax: 

DistinctItems = DISTINCTCOUNT( Items[c2] )

Hope this is what you after.

Regards,