Shape Map Areas with No Data

Greg_Deckler 

Sorry.  Not a Power BI expert by any means.  This is what you have done:

1. Added a separate table (tab) to the Excel spreadsheet, which is simply a list of all 50 states (Table (2)).  It has a single column.
2. Dynamically create another table (Table 2), which is a union of:
   1. The data from the original table (which was missing data for certain states)
   2. Data from every state NOT listed in the original table (Table (2) less the original table data), and for each of those states, set the other columns (date and color) to a default value that the states in the orignal table would not have.  For ex, color is Grey.
3. Create a calculated field of the Total Sightings in Table 2 for data where the color is not Gray, but if that would return nothing, return zero instead, giving every state with no entries in the original table Total Sightings of zero.

Then use the new table, Table 2, in the visualizations.  (I also need to tweak the existing color and date charts to filter out the zero states.)

Thank you for this!  The one challenge I will have applying this to my situation -- which is NOT about UFO Sightings, btw 🙂 -- is that I have a lot more columns and measures (perhaps 30 between what comes from Excel and the calculated ones).  Creating a new, dynamic table would be much more complex.  And I have many existing visualizations. I would need to change all of that to point to the new, dynamic table.  If there was a way to tweak a few of the existing columns and measures instead would be great.