Forum Discussion
FILTER and COUNTA function returning 1 even when no data is found
Hi,
I am using the formula below to track an unique count of Red Hat Enterprise OS which are in a specific migration wave. However, the formula is returning a count of "1" even though there are no Red Hat Enterprise OS's in the wave in question. How can I modify this formula to provide an accurate unique count of Red Hat devices?
=COUNTA(UNIQUE(FILTER(MasterServerToApp[Server], (MasterServerToApp[Wave] = B4) * ISNUMBER(SEARCH("Red Hat Enterprise", MasterServerToApp[OS Trim])))))
Below are two screenshots which prove there are "0" Red Hat Enterprise devices, however the dashboard still shows a value of "1"
Thanks,
Connor
ConnorAlign Try using the ROWS function wrapped in IFERROR:
=IFERROR(ROWS(UNIQUE(FILTER(MasterServerToApp[Server], (MasterServerToApp[Wave] = B4) * ISNUMBER(SEARCH("Red Hat Enterprise", MasterServerToApp[OS Trim]))))), 0)Or process the results of the include parameter first to make sure at least 1 record returns TRUE:
=LET( incl, (MasterServerToApp[Wave] = B4) * ISNUMBER(SEARCH("Red Hat Enterprise", MasterServerToApp[OS Trim])), IF(OR(incl), ROWS(UNIQUE(FILTER(MasterServerToApp[Server], incl))), 0) )
ConnorAlign Try using the ROWS function wrapped in IFERROR:
=IFERROR(ROWS(UNIQUE(FILTER(MasterServerToApp[Server], (MasterServerToApp[Wave] = B4) * ISNUMBER(SEARCH("Red Hat Enterprise", MasterServerToApp[OS Trim]))))), 0)Or process the results of the include parameter first to make sure at least 1 record returns TRUE:
=LET( incl, (MasterServerToApp[Wave] = B4) * ISNUMBER(SEARCH("Red Hat Enterprise", MasterServerToApp[OS Trim])), IF(OR(incl), ROWS(UNIQUE(FILTER(MasterServerToApp[Server], incl))), 0) )- The first formula worked, I appreciate the quick and dilligent response. Have a great rest of your week!
