Counting distinct number of records based on a condition in tableau

Question

Item  Location    Empty
    A         X        No
    B         X        No   
    C         X        No

The above table is generated by query

select it.item,lt.location,lt.isempty 
from locTable lt 
join itemTable it 
on lt.location = it.location

Item table has "item" and its "location" information where as LocTable has "location" and "isEmpty" information

My problem is when i generate a calculated field using the formula

Sum( CASE [isempty] when 'No' THEN 1 ELSE 0 END )

I am getting 3 as the SUM. But as we see from the table there is just 1 location which is "X" and the count should technically be just 1. How can i modify this so that the count is accurate i.e. check whether a location is empty or not and then report as 1 rather than just counting number of "isEmpty = No" from table.

Desired output

The total slots that are not Empty = 1. This should be the output. Not 3

Answer 1

Maybe you're looking for something like this:

Is this what you need? If so, there's no need for a CF.

PS: I had to post an answer in order to show you my screenshot