I have an excel spreadsheet that I am testing out the map visualization with. the total dataset is less than 40K lines in 4 columns (Quarter-Year, State, Zip, and Value). When I import the data into the PowerBI desktop application and attempt to use the Map visualization, it gives me an error that the Zip Codes contain too many values (even though there are only ~7000 unique values in the Zip column)
Also - if I want to do a map by State, how do I get PowerBI to recognize that "PR" is the abbreviation for Puerto Rico and not the Brazilian state of Parana (there are other cases of this as well)
One caveat to this approach is when using Analysis Services. There does not seem to be a manual way of doing this in Power BI.
I should be able to create an ad-hoc field that combines the data I need to map properly.
I created everything as a geographic data in the cube and specified it as it's matching category (city, state, zip), but this still does not help.
You really need specific instructions on how to handle this or make BING smarter.
I'm testing another product Tableau and it handles it without any problems.
I ended up adding another field to my Zip Code lookup and structure like I would mail a letter "City, State Zip".
Ran into one rather curious issue though, I added a filter for the State and it's not making the list unique.
So I get repeated values for the state from my zip code table. I shouldn't see NY,NY... just NY. Not sure why thisis happening
I agree, tableau handles the record volume just fine (I'm dealing with about 6M+ records). I really don't want to invest a huge amount of effort building a PowerBI solution if this issue is not fixed quickly. Once you see what is possible, you don't like to have to build work-arounds. What are others using as a work-around? ~Denise
I can't say tableau handles the large record volumes well.
Actually had a project recently which it totally balked on. Updates to each page were exceptionally slow and building linked tables proved unusable due to the volume of data.
I had to import the into Microsoft Sql Server Developer edition and create a smaller data set for it to work.
This project was for an online class using tableau.
If I had the choice I would used PowerBI, better structure for build of your data and display and distribution to various formats
Thanks for the response. I am doing a parrallel comparison. Our IT wants to use POWERBI, but I already had Tableau, so I am using tableau to proof the PowerBI results. Both are connected to the same 'normalized' table in an SQL Server database.
The result so far is that I cant even get PowerBI to include all of the results because it hits overload mode. Do I really have to create separate views to be able to create small-subsets of data? Actually... I haven't even tested if I can use views yet in PowerBI. Is that doable?
I do feel (from what I have seen so far) that Power BI might have better publishing capability (my company is in the middle of purchasing a server so that we can publish to it). That is why I need to stick with this, but it is not nearly as user friendly as Tableau. My fear is that if I can't report accurate/full summary of data, the publishing isn't going to matter.
Does anyone know why I can easily find location using the offical Bing Map when I can't seemt to find the same address using Bing Map via Power BI Map visualizations ?
I'm doing as most do I guess, that is I concatanate to get [Address & No - Zip code - City] and it seems to work >95% of the time (I want this to go up to close as 99.99%)
One thing to make sure of is to remove the hash symbol (#) from any address you pass in and concatenate. Leaving it in there has caused problems for me. You can remove at source or just filter it out in Power BI.
I have a problem with automatic zooming. When I filter data displayed on the map and only one point matches the criteria of the filter, it automatically zooms out. Is this a bug or is it meant to be like that? It's really anoying to scroll in everytime you filter the data.
I also wish that any "Too Many Values" or other warnings would just go away. Charts should be smart enough to merge several dots into one if they are too close to each other.
If the map will only show a sample of the zip codes, we don't know which zip codes are not being mapped so the chart becomes practically useless. I cannot think of a single scenario where those warnings do anything other than confuse the user who is looking at the chart.
The only time when those warnings are useful is during development. So maybe the solution is to show the warning during the Edit mode but not during the View mode.
Mapping can be a little flaky if you let it up to Bing to geocode everything. What you may want to do is to add a column to your dataset that will help Bing do a better job geocoding.
For example, if you have a column for a city and another column for a state, I would create a CityState column that would take Chicago and IL and turn it into “Chicago, IL”. You can enhance this further if geocoding still is not perfect by adding IF logic and turning PR into Puerto Rico. So if you have San Juan as a city and PR as a state, I would create a column that said “San Juan, Puerto Rico” and let service geocode that. My guess is that it would do a much better job.
This worked, thank you. I concatinated the city, state, and zip in a new column of my source spreadsheet. Then I saved it and "refreshed" the data in PowerBI. Once updated, I went to the "Data" menu on the left (the table looking icon) and highlighted the new column I made. Under the Modeling tab, I changed the "Data Category" to "Place".
Bingo....all US cities.
Now if I could only figure out how to add just the city names (not the concatinated city/state/zip) and the count for each city to the map visualization, I'd be golden!
>>Now if I could only figure out how to add just the city names (not the concatinated city/state/zip) and the count for each city to >>the map visualization, I'd be golden!
I would look at building a custom hierarchy. Might be another way to map this properly and still be able to get a city count
Seems clunky to have to add yet another column to my data when the tool is supposed to understand and use filters for geo-coding intelligently.
If the new column isn't stored in the DW, then every time end-users who are supposed to use the tool will run into the same problems.
Well, city names are not unique so if you city name is Paris, i would not expect the mapping engine to know which Paris it is. So adding a state to a city name helps with that.
When drill down is back, one thing I would expect to improve would be for the map to remember what state I drilled into so that it can only show the cities in that state without me having to add the state to the city name. Other than that, I think having a field in the model that uniquely identifies a location is probably the safest way to go about it.
What I did is concatenated Address, City, State, Zip and it plots points pretty well. This is good for customer level but you will run into value limits. Question is, what is the official limit?
Learn how to create your own user groups today!
Click here to read more about the October 2021 Release!
Check out the new Power Platform Community Connections gallery!
Mark your calendars and join us for our next Power BI Dev Camp!