SOLVED

FILTER and COUNTA function returning 1 even when no data is found

Copper Contributor

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"

 

 

ConnorAlign_0-1716309076597.png

ConnorAlign_1-1716309101315.png

 

Thanks,

Connor

3 Replies
best response confirmed by ConnorAlign (Copper Contributor)
Solution

@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!

@ConnorAlign 

As variant

=SUM( --NOT(ISERROR( UNIQUE( ...
1 best response

Accepted Solutions
best response confirmed by ConnorAlign (Copper Contributor)
Solution

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

 

View solution in original post