Count Distinct Collect only returns 1, even when there should be none

Karen Harker

I have this formula for a Sheet Summary field:

=COUNT(DISTINCT(COLLECT(Goal:Goal, [Primary SD]:[Primary SD], ="SET", [Fiscal Year]:[Fiscal Year], ="FY2022-2023")))

Attached is a screenshot of the sheet. It is filtered to show only rows with Primary SD=SET, and you can see that there are none for FY2022-2023. So the Sheet Summary field should be zero. I have similar SS fields for other Primary SD's and other years and other targets to count (e.g. Goals, Metrics, and Initiatives). But they all return 1.

I'm stumped.

Carson Penticuff

I believe all rows will be evaluated by your sheet summary formula, even rows not visible due to a filter.

Karen Harker

That makes sense. But the filter was only applied here to demonstrate that the result of the function should be zero, not 1. Why is this result, indeed all results regardless of the actual counts, always 1?

Razetto

@Karen Harker Your formula works well which makes me think that somehow the error has to do with the fiscal year being a formula column.

Karen Harker

Well, that's weird...now the formula works! I guess it needed to think about it overnight ;). Perhaps it needed to be refreshed. Anyhow - all is good.

Thank you both for responding.

Karen