Count Distinct Values for a group of list items

[Anonymous]

Hi Community,

I am stuck with an issue, but maybe just cannot see the wood for the trees.

In my sharepoint list I maintain Facilities and Certificates. Each Facility can have more than one certificate.

I want to count the amount of certificates, but group them first (e.g. 9001 and 13485)

My current flow looks like this:

1) Get List

2) Filter by ISO9001 and 13485 using: @OR(contains(item()?['Certification/Value'], 'ISO 9001'),contains(item()?['Certification/Value'], 'ISO 13485'))

3) Distinct by using union(body('Select_13485'),body('Select_13485'))

Even with the distinct every certificate is counted twice when I do length(outputs('Distinct_900113485'))

Can anyone help me where I am wrong and how to get at the right path?

Thank you

[fchopo]

Hi @Anonymous 

Sorry, I was confused!!

Before applying the union operator, which fields are you using in the select? (are you only selecting the facility field?)

Regards,

Ferran

[Anonymous]

Hi @fchopo 

Huh? Are you sure that this function exists?

template function 'distinct' is not defined or not valid.'.

[fchopo]

Hi @Fn7a 

Sorry, I was confused!!

Before applying the union operator, which fields are you using in the select? (are you only selecting the facility field?)

Regards,

[Anonymous]

Hi @fchopo 

i am just using 'Certification/Value' in my select with checking if ISO 9001 or ISO 13485 are present by using

@Or(contains(item()?['Certification/Value'], 'ISO 9001'),contains(item()?['Certification/Value'], 'ISO 13485'))

There is no other select before that action. Just the get list action filtering on all items with a certain status (Filter query Status eq 'valid') 

[fchopo · Accepted solution]

OK!

After the select, and before applying the union function, could you add another select, and select only the Facility field?

The union (distinct) function is returning all rows, because the function is comparing all columns (so all rows are different), and you need to apply the distinct function only to the Facility column.

Hope it helps!
Ferran

[Anonymous]

Hi @fchopo 

I followed your suggestion and did this:

My result returns 21. Which is correct compared to my excel calculation:

Thanks a lot for your help!