cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fn7a
Frequent Visitor

Count Distinct Values for a group of list items

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)

 

Fn7a_0-1639671063565.png

 

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'))

 

Fn7a_1-1639671080290.png

 

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
fchopo
Super User
Super User

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

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!

View solution in original post

6 REPLIES 6
fchopo
Super User
Super User

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,

Ferran

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!
Fn7a
Frequent Visitor

Hi @fchopo 

Huh? Are you sure that this function exists?

 

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

fchopo
Super User
Super User

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,

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!
Fn7a
Frequent Visitor

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'))

 

 

Fn7a_0-1639743420787.png

 

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
Super User
Super User

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

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!
Fn7a
Frequent Visitor

Hi @fchopo 

I followed your suggestion and did this:

 

Fn7a_3-1639979335689.png

 

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

Fn7a_4-1639979705489.png

 

Thanks a lot for your help!

 

Helpful resources

Announcements
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Users online (1,484)