Learn how to use sheet swapping in Tableau to seamlessly drill down or back up between different regional granularities (i.e. country, state, city, zip code). You’ll also learn why this is needed, some design tips for both filled and symbol maps, and how to use dummy values for better parameter actions.
Hi, this is Ryan with Playfair+. And in this video, I’m going to show you how to zoom into multiple geographies on a Tableau map and have that zoom to appear to happen on a single sheet. Also along the way, going to give you a couple of design tips on how to make maps in Tableau.
By the end of this video, you’ll be able to make a similar user experience to what you see on the screen here. Notice it says Sales by State and City. So we’ve got this filled map looking at Sales by State. But if I click on any of those states, it’s going to zoom into that individual state and show you Sales by City.
To return back out to the first map, you simply click on a city again. And we’re now back at the State level. To get started over here in Tableau Desktop, the first thing we need are the maps themselves. So I’ll start with my filled map Sales by State just by double clicking on the State dimension.
And then if I change the mark type to Map and drag (Sales) to (Color), we have a filled map. Couple of little things I want to show you before I move on to the Sales by City map. First, I’ve made this map, Sales by State, but I’m seeing an indicator that says 10 unknown. As of Tableau version 2022.2, they finally added another country to the default Sample Superstore dataset in the US so that we now have both US states and Canadian provinces, but it does cause this little error message if you’re using the default sample dataset.
To clean that up, you could add a filter to any individual sheet or you can navigate back to the data source tab, which is where you’re connecting to the data, and you can just add a filter at the data source level for Country. So if I choose Country and US only, now when I go back to my Sales by State map, the 10 provinces that were in here are no longer in the connection. And it recognizes all of the US states.
Another thing you might be wondering is, why do we need this technique? I’ve shown you in other resources here at Playfair+ how to use a parameter to dynamically choose measures or dimensions. Well, let me show you why that’s not going to work for maps. So let’s say that I had a parameter. And I’ll call this p. State / City.
And it’s a very specific list of just two choices, so I’ll choose List for the allowable values. On one row, I’ll type State– oops, I need to change the data type to String first. On one row, I’ll type State. And on the second row, I’ll type City. So that is the first step. You’re going to need that parameter. So go ahead and copy that step. But I also wanted to show you why the traditional technique does not work for maps. So I’ll click OK.
We have a parameter. So one way you think that this might work– I’ll call this State or City– is to use a case statement that says, when it’s p. State / City and we want to say when State is selected, then show State or Province on the next row. I might say when City then show City, END.
Tableau tells me the calculation is valid. That is the type of logic that I use when I’m trying to let my users select dimensions dynamically. I’ll share that in the related content below the video. But it’s not going to work for maps, and that’s because the field used to generate maps is not technically one of the six data types. It’s actually a geographic role.
So let me click OK and show you that any time you see a little globe icon next to the field, that’s not actually a data type, but it’s a data type technically of String with a geographic role assigned to it. And you can only have one geographic role assigned to something at a time. So you can see State or City, it thinks it’s a String.
I could go in here and assign a geographic role, but I would have to choose either State or City. So it’s not going to dynamically change it for me like I’m wanting to do. That’s where this tricky technique and why you’re a member of Playfair+ comes in. I’m going to show you how to get around that. So you can create this illusion of a single sheet drilldown between these different geographic roles.
I’m actually going to delete that one so it doesn’t confuse me later on. And I’ll go ahead and finish my Sales by City map. This time, I’ll just double click city, and I will size those by Sales. I’m seeing the indicator again. That’s because I haven’t told Tableau how to pair the cities with the states that they’re in.
I can get around that issue by also dragging State to Detail. And lastly, let’s format these a little bit. I also told you I was going to give you a couple of design tips on maps. For my filled map, first of all, I’m going to put the sequential color palette in brand just by double clicking on the color legend, giving this a nicer green color for the highest values.
And another aesthetic I like is to change the border– whenever I’m using a filled map and I have this nice polygon around the entire state border, I like to make the color of that border match the color of the mark. Just gives it a subtle, I think, pretty clean design polish.
And then when I’ve got a filled map, I don’t really care much for these backgrounds at all. If my company is based in the US and I’m looking at just the US map, these city names in Mexico are not adding a lot of value to the analysis. I can really get rid of those altogether by navigating to Map in the top navigation and choosing Background Layers. And I’ll just wash that whole background image out completely.
And I think that’s just a really nice-looking filled map there. Do something similar here on the Sales by City by first changing the color of the symbols to that in-brand green. I’ll also increase the size of those circles so that they stand out on the view a little bit more. And then here’s a nice little tricky thing you can do now that Tableau has added layers, the ability to add map layers to a map like this.
I could actually make this symbol map look very similar to this filled map by dragging another layer onto the view. So this one will be at the State level. And as I start to drag that onto the view, this indicator or this message pops up that says I can Add a Marks Layer. And if I let go of State there, I’ve now got two layers.
The City layer is the first one I created. I’ve now just added State as well. And these are independent marks cards, which means I can change the mark types independently. One layer can be a symbol map while the other layer can be a filled map. You can kind of see that coming together.
Maybe I’ll make the color of all the states white but give it a border color that matches the other marks. So now I’ve got a filled map. It’s laying right on top of the symbol map. I can change the order of those layers by left clicking on one layer and dragging it above or below the other over there on the Marks cards.
When I let go, I’ve got this nice-looking dual-axis map now. Maybe the last thing I’ll do on City is add some white borders to make those circles pop a little bit more. All right. So now we’ve got our different levels of geography independently on different sheets. The trick to getting this illusion of a single sheet zoom, even when you’re using maps, is to use what I call traditional sheet swapping.
We show you both how to do traditional sheet swapping as well as a new easy way that uses a Show/Hide button. But this is the very classic way of doing what’s called sheet swapping in Tableau. I’ll quickly show you that again here. What it involves is displaying one sheet at a time.
And we want the sheet that’s being displayed to be tied to this parameter. When State is being shown in the parameter, I want to only see this sheet. When City is being selected, I only want to see the City sheet. But as you can see, by default, whatever I choose, I’m seeing– the map is still there.
Well, to get around that, you can add a filter. So for this first map, I’m going to drag my State dimension to the Filters shelf, and I’m going to navigate to the Condition tab. And I’m just going to write a simple formula that says p. State / City must equal State. So that is the entire formula. I’ll click OK.
And now when I go to City, this map will disappear. When State is selected, I see the map. When City is selected, it disappears. I want to do the same thing over here on the Sales by City map. So I will drag City– actually, I’ll do this at the State level again just to make sure it captures both the background filled map and then the symbol map by city.
So State or Province, Condition tab. Same parameter, but, this time, I need to ensure I say that it equals City. And I’ll click OK. And if I did that right– I don’t see anything change yet. That’s because the current value of the parameter is City. But if I choose State, the map disappears. So that’s the foundation of traditional sheet swapping.
It’s going to show one sheet at a time. When State is selected, the State map shows up. If I change that to City, the City map will appear, but the State map now disappears. To finalize traditional sheet swapping, we need to put both of these sheets onto a dashboard. So I’ll make a dashboard. And the trick to getting this to work is we’re going to place both of those sheets within a vertical layout container.
So that I can best control the size and location of that container, I’m going to make it floating. And I’ll choose Vertical. You technically could use either Horizontal or Vertical, but these I like to stack on top of each other. So I’ll drag Vertical onto the view, kind of just roughly give it some larger dimensions and center this on the view. Of course, you can get much more precise with this.
But that blue border is showing me where there is a layout container. I’m now going to place the filled map Sales by State into the container. To do this with floating objects, you drag the object close to the container and then hold the Shift key. When you hold the Shift key, it’s showing you that it’s going to place it within that container. And let go.
I’m going to do the same thing and place symbol map right underneath my filled map. The trick is you need to ensure that they are going into the same container. And I can tell they’re in the same container. There’s a couple of indicators. There’s a blue border telling me the sheet is going into a container. There’s also a dashed line showing me where that sheet is going to be placed within the container. So that’s exactly where I want to place it. I’ll let go.
So right now, we’ve got a filled map. It’s on top. It’s not being shown because we added that filter that only makes that sheet appear when State is selected. We do see City. One last thing that’s driving me crazy there– I forgot to match the background and just wash that out completely. So I’ll do that so it’s nice and clean.
And now when I change that parameter dropdown, State will appear, and City goes away, and vice versa. So I no longer actually need any of this stuff on the right. I’ll just select that whole container and delete it. Last thing with this technique before I show you how to hook up the zoom functionality.
With traditional sheet swapping, it’s always advised to go ahead and hide the sheet titles. Otherwise, it appears that something is there, and people might think something is broken. To better create this illusion that we’re on a single sheet, just hide both of those, which you can do by right clicking each one and clicking Hide Title.
Last thing. Back to my Sales by City. I want to add one more filter to this. I want to filter this just based on the state that is selected in the first view. So a couple steps to do that. I want to create a parameter. The easiest way to do that is to right click directly on the dimension that I want to create the parameter from, hover over Create, and choose Create Parameter.
There’s a couple of benefits to doing it that way. There’s a little bit of a shortcut. It makes the parameter the same data type as the dimension that it was created from, and it populates the list of allowable values with the dimension members that were inside of that dimension. That would have been very cumbersome to have to try to type out all of the US state names.
So that is the entire parameter. The only thing left for me is I might want to give this a better name. I always like to write my parameters with the syntax p. and then whatever I want to call it. So p. State, that just helps me find it later on when I go to use it in a calculated field.
So that’s the entire parameter. I’m then going to make a Boolean filter that says State = p. State. And that actually is the entire formula. So I’ll just retype that again. State/Province equals my p. State parameter. And the reason that I’m doing this is if I add this calculation to the Filters shelf, it’s only going to keep the current value of that p. State parameter.
And that will come into play later on whenever we want to pass a value from the State map to the filter, the zoomed in view that’s looking at it by city. That’s going to come into play there. So I’ll choose– let me show you one more time what I did. Just created a Boolean filter. Add that to the Filters shelf, and choose True. This should leave us with just one state on the map.
And that state is going to match whatever the current value of the p. State parameter is. We’re going to change that later on. But by default, it is Alabama, just because that’s the first allowable value in that parameter. All right. Let’s glance back at the dashboard, see how we’re doing.
We’ve got all the pieces in place but the tricky part of this technique is to get from this view within the sheet swapping that’s currently showing me the City level of granularity. To get that back to State, I need to somehow change the parameter value of that p. State/City from City to State.
I could, of course, show the parameter control. That’s no fun. We’re kind of faking Tableau out, creating these really elegant user experiences. So got a trick for you to get around that. But we do need one extra field for this view. And that field is going to include just one value called State.
Well, I don’t have a value in the Sample Superstore dataset that just has a single value called State, but there’s a trick for this. You can just create a calculated field. If you ever just need a single value, just call the name of the field whatever the value is. So if it’s State, I’ll call it State Value. And then you literally just type that value in quotation marks.
And when I click OK, you’ll see I have a new dimension. It has a data type of String. And if I describe that dimension, it has a single value in it called State. If I place that on to the Detail property of the City Marks card, that value of State is now on every single mark. And you can see that confirmed when I hover over any of the marks, the tooltip shows me that the value of State is within that tooltip.
Why that’s important is now, back here on my dashboard, I can add a parameter action to my City map. So when you click the symbol map Sales by City, I want you to target the City or State parameter, and I want you to pass that State value. If I had not created that dimension, that fake dummy dimension and placed it on the Detail property, it would not be available here. So ensure that that’s on Detail.
But now that it is, that’s an option for me. And if I click OK, click OK again. Now, if I click on any of the cities, it’s going to pass the State value to that parameter, activating the sheet swapping, and it’s going to zoom back out to the entire view of the US. I need to do something similar on the State map.
I’ll go to that sheet and make a calculated field with my City value. I will place that onto the Detail property of the Marks card. Confirm it’s there just by hovering over. Of course, you can take this a step further, later clean this up. You don’t need to display that to your end user.
But just to show you that City is now part of that view, that means it’s going to be available to me when I go to add another parameter action. And I probably should get better about naming these. This is my State to City. And I want it to run on Select of my filled map. I’m targeting the p. State/City parameter.
I’m passing the City value. And the aggregation, by the way, is none. That’s because the data type is String. We’re not adding those up in any way. So that’s good. My parameter is OK. I’ll click OK twice to close that. Now, if I click on a state, it goes to the City map. If I click on a city, it goes back to the State map.
However, you saw I clicked on California and it still shows Alabama. That’s because on the City map, that current value of p. State is stuck at Alabama. So I need one more parameter action on my dashboard. We’re going to say that if you click on the State map, I also want you to pass not only the City value, but the State value that was clicked on to that p. State parameter.
So I will change the parameter value, and we’ll call this my State Filter. This one will run only on my filled map. I do want it to run on Select, which is synonymous with click. This time, I’m targeting not the p. State/City, but the p. State. That was the second parameter that we created.
And this field is not the City value, but it’s the State name itself. So just slightly different. Just third and final parameter action. Now that we’ve got that connected, I’ll go to Presentation mode. I’ll reset my view once by clicking any city, but I’ve got by default my state-level view. Those polygons are colored by sales performance.
If I click a state like Texas, Texas gets drilled in to the City level view. I’m now looking at Sales by City for all the different cities in the state of Texas. I can reset the view at any time by clicking on any of the cities, and it zooms back out to the state-level view, appearing to all be on a single sheet.
This has been Ryan with Playfair Plus – thanks for watching!