Count Distinct Collect - Unique Values Several Columns - Help

Hello!

I am trying to get the unique number of countries that have a value greater than 0 from a set of columns, the formula works, if I just collect from one column, example below, I get what I want, but if I try to add Carlos's Colum or others to the formula, then it does not work or just returns 0 or 1.

=COUNT(DISTINCT(COLLECT(Countries:Countries, [Melissa's Moves]:[Melissa's Moves], >0)))

The above is the one that works, if I try any of the below, it fails

=COUNT(DISTINCT(COLLECT(Countries:Countries, [Melissa's Moves]:[Melissa's Moves], >0, Countries:Countries, [Carlos's Moves]:[Carlos's Moves], >0)))

=COUNT(DISTINCT(COLLECT(Countries:Countries, [Melissa's Moves]:[Melissa's Moves], Countries:Countries, [Carlos's Moves]:[Carlos's Moves], >0)))

I am trying to show the total unique number of countries for which there are active moves greater than 0 in each column

Any ideas on what might be the correct formula?

Thanks!

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

You would need to insert a helper column and have the row's total on each. Then you would be able to reference the helper column and the country column.

All comments

Josue RQ

I kept playing around and I am closer but now with the challenge that I think I might have duplicates

COUNTM(COLLECT(Countries:Countries, [Melissa's Moves]:[Melissa's Moves], >0)) + COUNTM(COLLECT(Countries:Countries, [Carlos's Moves]:[Carlos's Moves], >0))

It gives also what I need but I need to find a way to only count unique between the two

Josue RQ

Lord have mercy.... I was able to do it with a report lol

If anyone knows how to get a formula to do this I would be interested in learning, thank you!

Paul Newcome

You would need to insert a helper column and have the row's total on each. Then you would be able to reference the helper column and the country column.

Josue RQ

@Paul Newcome monuments should be raised in your honor! You are the best! I had the totals already and I didn't think of doing that.

THANK YOU!

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.