Have you tried to Pivot both State and City together? This way, you get only one dimension rather than 2 different dimensions. If it doesn't help, please specify your requirements.
I too have a similar requirement:
I have cities in the data but no states are present and i wish to plot states on the map. How can I map the states with the given cities? Any idea?
There is one issue of not having state as some cities could be also in more than 1 state. For example, Kansas City, Kansas and Kansas City, Missouri, or Charleston West Virginia and Charleston South Carolina.
So, as you can see, without state or zip code, the same city can be attributed more than one state and it is misleading. Without seeing your sample data, it is hard to make any specific instructions, but I have answered something very similar here: How do I resolve unknown/unrecognized cities?
You can also utilize https://geocod.io/ to convert address to lat and long if some more info provided.
I am aware that 10.4 version improved geo-coding feature and included more cities that Tableau recognizes without any geo-coding. So, you have to either manually lookup state or zip codes for those cities or you could try and see if those batch geocoding software could convert just cities to lat and long coordinates.
I hope it helps.
Hi Marc, I just hit the same problem of having states only for U.S. cities, and this forum post link below solved the problem. You create a dual-axis map and map U.S. cities with states one way (city/state/country) and all other cities another way (city/country). Not perfect but resolves a lot of ambiguous locations with messy data. Regards, Zach.
I had a similar issue.
Let's say you have City, State and Country,
Created a calculation State1 where ifnull([State], [City])
Now use City, State1 and Country to plot.