Retain Map Borders and Tooltips when Map is Filtered
Published: 20 Mar 2017 Last Modified Date: 31 Dec 2019
How to retain map mark borders and tooltips even when the map is filtered.
For example, the map should always show all states, but when you filter down to Washington and Oregon, only the selected should be colored by SUM(Sales). The rest of the states should show as blank outlines.
Option 1: Use a Parameter to Conditionally Color States
Note: this option only allows selecting either a single state or all states.
Right-click [State] in the data pane and select Create > Parameter…
In the Create Parameter dialog, add "All" to the list of values and click OK
Right-click [State Parameter] in the data pane and select Show Parameter Control
Create a calculated field with a name like "Color Calculation" with a calculation similar to the following: IF [State] = [State Parameter] OR [State Parameter] = "All" THEN [Sales] END
Drag [Color Calculation] to Color
Option 2: Duplicate the Data Set by Joining to a Dummy Table and then Conditionally Filter Only One Duplicate of the Data Set
Note: This method may create performance issues due to the data duplication. Also there will be a value in the filter which cannot be deselected.
Create a dummy table in Excel with two rows. See the attached "dummy.xlsx", which I used in this example.
Navigate to the data source tab
Add a connection to the "dummy.xlsx" Excel worksheet with dummy data
In the Join dialog, do the following:
Click Add new join clause
Click Create Join Calculation
Type 1 in the calculation editor and click OK
under Sheet1, select Dummy Link ID
Navigate to the map worksheet
Create a calculated field with a name like "State Filter" with a calculation similar to the following: IF [Dummy Duplicating Value] = 1 THEN [State] ELSE "!Do not deselect this option" END
Right-click [State Filter] in the data pane and select Show Filter. For testing, unselect one or two states.
Create a calculated field with a name like "Color Calculation" with a calculation similar to the following: IF [Dummy Duplicating Value] = 1 THEN [Sales] END
Drag [Color Calculation] to Color on the marks card