Generate List of Locations who have not submitted a survey

[bmccaslin]

I have a treemap that shows all the locations within the data that have not submitted a survey on the slicer selected date. The formula works up until a specific date - where after that I am getting all blanks. 

Current formula:

count = countrows('Location Surveys')

no submission = countx(values(locations[locations]), if(isblank([count]), 1, blank()))

locations[locations] are:

Arizona

California

Oregon

Texas

New Mexico

Colorado

Idaho

Washington

'Location Surveys' Submitted Oct 20th:

Idaho

Washington

New Mexico

Colorado

Missing:

Arizona

California

Oregon

Texas

For example, if I select October 19th in the slicer - the treemap updates and shows all the locations that did not submit their survey that day. However, if I select October 20th, the treemap goes blank. Validating the data - I have 4 sites that did not submit the survey that day, so they should be showing up. 

I have no date restrictions on any of the pages. The calendar table is a calculated table and has the octoer 20th date there. Is there something I am missing or is there another way to go about getting the data and having it update when data is refreshed?

[lbendlin · Accepted solution]

To report on things that are not there you need to use disconnected tables and/or crossjoins

Then you need to use a visual that can handle empty rows. The treemap is not the right visual for that.