Shape Map Areas with No Data

[msalamon]

I have added a shape map of the US to track information for every state.  For example (and for fun), imagine that I am tracking the number of UFO sightings per state, and in other visualizations on the page, tracking additional information about those UFO sitings (shape of the craft, whether the viewer was taken aboard, etc.).

Some states have many sightings (like NV, due to Area 51), others have none (like NJ, because we don't believe in that stuff 🙂 ).  The map has custom tooltips showing the number of complaints for each state and clicking on a state will filter the data on all of the visualizations in the page to be limited to that one state.  The map also has diverging colors based on the number of sightings in each state.

However, if a state has ZERO sightings, that state does not work like the other states in two ways:

1- Instead of using the custom tooltips, hovering over a zero-sighting state show a basic tooltip (not a custom tooltip) with simply: "State [StateName]".  I want those states' tooltips to be like the others in format and data and say:  "Number of sightings  0".

2- The zero-sighting states are clickable.  As soon as you click on such a state, you can see all of the other visualizations attempt to update themselves to match the selected state (you see the spinners), but then nothing changes, because there is no data associated with a zero-sightings state, and those visualizations continue to show the full data.  Because those state have no sightings, I would prefer that those states not be clickable at all, as there is no benefit to clicking on them.  However, if that is not possible, then clicking on a zero-sighting state should empty out the other visualizations on the page, because there is no matching data at all.  

I suspect I could fix this by somehow adding a zero entry for those states, rather than no entries.  But Power BI is doing the aggregation per state, so there is no way that I can see to filing these "gaps".

Is there any way to achieve either or both goals?

[Greg_Deckler]

@msalamon I didn't forget about you! OK, the way to fix this is to add a row in for all states. Then you can do a little DAX magic to end up with 0 for states that have no sightings. Take a look, this isn't great because I had to do some work-a-rounds because I didn't have your excel file. I would use Table (2) and Merge it with your original table to create a new table with the missing states and go from there but hope you get the idea. You want Page 2 of attached PBIX below sig.

[msalamon]

@Greg_Deckler   Thanks!  I will take a look at this.

Btw, here is the original excel file:  https://drive.google.com/file/d/1OJiP8O1_7GfTaoBUEsHIreoeZvpoe49Z/view?usp=sharing

[Greg_Deckler]

@msalamon No access

[msalamon]

@Greg_Deckler try again?

[Greg_Deckler]

@msalamon Yep, was able to download, do you want me to give this a shot again using your Excel file or did my previous PBIX work out enough for you?

[msalamon]

@Greg_Deckler i would be curious what a solution is like with a single excel.  that would be great.

[Greg_Deckler · Accepted solution]

@msalamon See attached.

[msalamon]

@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.

[Greg_Deckler]

@msalamon - Its difficult to say for sure without experimenting. Since it's UFO data, can you share the PBIX?

[msalamon]

@Greg_Deckler 

Here is the pbix: https://drive.google.com/file/d/1kFVillE90t5G7yhZ_4M4So3CBhMjFDJ_/view?usp=sharing

(I updated the link so that it does not require approval to access.)

This is not the actual data or pbix I am using. I threw the data and pbix together quickly to demonstrate.

1. I added a custom tooltip so you can see the difference between the states with data and the states without.
2. Also, it's hard to see because there is not a lot of data to recalculate, but when you click on a state with no data, notice that the other visualizations attempt to update themselves (you'll see the spinner briefly) but nothing updates.