Count Distinct Collect only returns 1, even when there should be none
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.
Help Article Resources
Check out the Formula Handbook template!