cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

Calculate a number of occurrences of unique items within column

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!

1 ACCEPTED SOLUTION
Super User

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,

3 REPLIES 3
Super User

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,

Frequent Visitor

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.

Super User

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,

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.