> I only get zero values when its definitely not the case.
It definitely IS the case. How can you have a record for which [LocationName]="A" AND [LocationName]="B"? That is saying count the rows where "A"="B" - which will always be zero.
MAybe you need OR instead of AND? If not, maybe you can explain what you are trying to count?
A single record can appear multiple times in the table. For example, record 1 has only one row, and has location A. While record 2 has 2 rows, one with location A and one with location B.
You are right. Since LocationName is a single column, its only looking at the single entry and always getting either 4 or 5. I guess my data needs to have a distinct column for each LocationName for this to work?
Depends on your situation, but maybe something like:
IF FIRST()=0 THEN WINDOW_SUM(SUM(IIF([LocationName]="A" OR [LocationName]="B",1,0))-1,0,IIF(FIRST()=0,LAST(),0)) END
with "recordID" on the Level of Detail shelf, and "recordID" selected as the Compute using for this custom table calculation could work for you (depending on the setup of your worksheet).
Attached is an example of this. Only recordID 2 and 5 have both A and B, so the count returns 2.
If you want to add a third location:
IF FIRST()=0 THEN WINDOW_SUM(SUM(IIF([LocationName]="A" OR [LocationName]="B" OR [LocationName]="C",1,0))-2,0,IIF(FIRST()=0,LAST(),0)) END
notice that the -1 changed to a -2 in addition to an additional OR conditional statement.
If this does not work for your situation, can you provide a sample packaged workbook that represents your situation?
recrod_count.twbx 15.5 KB