Doing a UNIQUE Count by Server and OS.

[Frank145]

Hi Team

I am doing I am doing a COUNTA using UNIQUE of devices in another table.

=COUNTA(UNIQUE(FILTER(MasterServerToApp[Server],MasterServerToApp[Wave]=B4)))

What I am trying to do is determine out of those devices which Operating systems is running on them. I did this with a helper column and using this formula. =UNIQUE(COUNTIFS(MasterServerToApp[OS Trim],"*Microsoft Windows",MasterServerToApp[Wave],B4))

The problem being the number are not adding up to what the first formula is providing. Is there any way to combine the two formulas where is pulls the unique count from the "Server" column and pulls in the "OS Trim" column to give me the unique count of Windows machines?

Dashboard Results. As you can see the number of devices is 17, but is over counting by 3.

[#2]

@Frank145 

=COUNTA(UNIQUE(FILTER(MasterServerToApp[OS Trim], ISNUMBER(SEARCH("Microsoft Windows", MasterServerToApp[OS Trim]))*(MasterServerToApp[Wave]=B4))))

[#3]

Hi Hans; that worked great; the only problem is if the data is not there the formula still returns a "1".

For example if I had no "Microsoft Windows" and only "Red Hat" it will still return a 1, even though the count is empty for "Microsoft Windows"

Is there any way of ignoring the 1 and turn that into a 0 if nothing is found?

[#4]

@Frank145Try this:

=IF(SUM(ISNUMBER(SEARCH("Microsoft Windows", MasterServerToApp[OS Trim]))*(MasterServerToApp[Wave]=B4))=0, 0, COUNTA(UNIQUE(FILTER(MasterServerToApp[OS Trim], ISNUMBER(SEARCH("Microsoft Windows", MasterServerToApp[OS Trim]))*(MasterServerToApp[Wave]=B4)))))