A Game-Changer for Map Visuals in Power BI | Distance Filtering

[@parivar237]

You are like a mentor to me. Through you, I have learned a lot about Power BI. I have been working with Power BI for the last two years, but I have gained detailed knowledge and background information through you. I hope that you will continue to provide such good knowledge in the future as well.

[@thermalgaming8049]

Happy to see that you have dropped three videos in last 7 days. Great channel to master power BI❤

[@HowtoPowerBI]

Good observation ✌️

[@8g8819]

Great, I did the same in the past by calculating the distances between the town and shops with Pyhton and added a column "distance" to the table (every town center vs every shop distance). Then, once loaded, you could use that distance column as a slicer selecting the town in another filter first. Of course, this is helpful when your dataset is very big. Instead of calculating distance on the fly with DAX you pre-calcultlate it before lodading the table in PBI. You gain something in the performance of the dashboard. Of course you need to set up a proper ETL pipeline for doing this also on the new data.

[@martinbubenheimer6289]

Nice! You could even extend the bubble size measure to represent the selected radius for the city bubble. Realistically, business users having seen this technique will ask to implement it with thousands of locations. I'm curious to see the performance in this case and I'm concerned that it will either exceed memory limits or cut off datapoints by a TOPN limitation in the backend query, so you get a "random" subset of valid locations. With a limited number of cities and a large number of locations you could also pre-calculate all distances between each city and each location in a fact table and then use filter context to select the matching locations, which will result in way less datapoints that need to be calculated. With the measure approach, having 10000 locations and a dozen of them are within the radius, Power BI still needs to calculate the distance measure and the filter measure for 10000 datapoints because filtering happens after the calculation. Having a large number of both, cities and locations, you will probably also have more luck using precalculated distances rather than the measures, although the table size will increase. But even having 1000 cities and 10000 location, the distances table would have 10000000 rows which shouldn't be a problem when modelled correctly.

[@oricchannel2811]

Bas, simply the best ! Thank you for sharing !!!

[@neorigby]

This is a brilliant step by step, really helped with my work, you my friend are a genius

[@phuongthaonguyen394]

What a wonderful video! I can learn so much from it. Thank you for your video.

[@darwinc2038]

Amazing Bas !!! I need to save to take your training You always have incredible ideas Greetings from Ecuador

[@narendrababu2287]

You can also visualise the stores with the sales revenue it generates. So, you can see different sizes depending on the sales. All we can do is just utilising the features available. As always I love Bas videos ❤❤

[@PaulEBrownbill]

Love the Power Bi maps. thanks Paul

[@DamianHomziuk-up4fc]

such an amazing trick! but i have a question - i saw that once you filtered Dusseldorf, on the bottom of the table there is a store (30026) in the same city, but over 70km from your target location, also for Berlin (20018 -> 76,8km) is that a matter of distance measure, or wrong Long/Lat in table?

[@Da_P-d1n]

Хорошо, что есть pbix. Без этого файла у меня не получалось. А теперь всё волшебство открылось.

[@perfectionist6624]

Always I am learning something new from your vidoes. You are the supper dupper hit Bas.... Thanks for giving so much valuable content for us... Love your work 💌

[@kimberlyprice5046]

Would love to see a video on best practices when working with multiple flat file excel sheets as the data sources with the same row cardinality but different column dimensions. Do you merge into one flat file then build the star schema? Do you setup a fact table of just the cardinality rows then join each dimension table to it, removing redundant columns? What about if you need to do a lot of cross table calculations and filtering, is it worth it to do flat in that case instead?

[@beetlesrule]

Been using this for years, and also made some modifications for additional selections

[@maansaleh4699]

That’s amazing, I would be happy to see you posting a video about making wow sales funnel

[@ManjunathChalapadi]

You are Master with power B!

[@irynavilner4533]

This is a great video! Thank you!

[@FitnessFoodRecipes]

Hey, I’m having issues with the map feature in my report. I’m using the free desktop version and wondering is that why the map visual doesn’t show up anymore even though it was visible when I opened my report a few days ago? I have turned on all the necessary settings in the Global settings . Any advice?

[@jakeonash]

As always, your videos are brilliant. Where did you get the latitude and longitude for the city and store locations? Is the city lat / long the centre of the city? I use UK postcodes, and sometimes, they convert to very odd locations in the world. For example, the postcode for one of our customers, the US Ambassador's Residence in London, shows as an address in Georgia. Cheers Jake

[@redlightning0001]

You need to convert the postcodes to lat/long. There are tools online but obviously you don’t put any identifying information. If not you can make script in python to convert them.

[@jakeonash]

@@redlightning0001 Cheers Mate

[@jpfm79]

Amazing,thank you for share your knowledge

[@lionelbennaceur1626]

Demystifying a complex subject, THX! Does this work well with lot of data points?

[@HowtoPowerBI]

Didnt have any problems so far.. if you do then you could go for a more straightforward distance formula, which is a bit less exact but quicker

[@chrisarnold769]

I've done this at work with ~ 190k people & 7-9k installations. So, I don't know how he defines 'a lot' but that's enough for most.

[@isospinofficial]

Amazing stuff Bas, thanks!

[@tony-ql4wv]

I notice you had a flow map power bi, did you ever encounter the issue where the latitude and longitutude becomes counts seem to be an issue for me.

[@AjaySingh-hp6eh]

Your videos are really awesome, however I always find it hard to get the data files used in your video to try along to have a better understanding

[@HowtoPowerBI]

I have all videos with download files listed on this page datatraining.io/powerbi-how-to .. (for this one it still has to be uploaded as it just has been published)

[@ssomtom]

Amazing :)

[@waqarashraf4934]

Very nice.. I did this a little while back but you made it look so simple haha

[@HowtoPowerBI]

🤓💻 thx!

[@thienquangnguyen2054]

That would be great! Thanks so much

[@miksosnowy1038]

Intresting video for me. similar to maps, there can be visualisation of layouts. Do you have a video that explains how to easily create a factory layout in Power BI? Specifically, I’m looking to visualize a production line with 200 machines and highlight the machines that are not working in red. The data can come from a database, but the visualization should be in Power BI. I am only interested in a single-floor layout and do not need details on how to display multiple floors. I know there are other visualization tools for such layouts, but is it possible to do this in a simple way in Power BI?

[@HowtoPowerBI]

Here u go :) Turn Any IMAGE into an INTERACTIVE VISUAL in Power BI kzbin.info/www/bejne/oXKwmZttbJuJbtk

[@unnikrishnansanthosh]

super cool, thank you for sharing

[@violatorwashere]

Hi Bas, I love the stuff you make. I have an interesting case for you. Can you make a video about a specific challenge? Imagine an excel file, which connects to two different SQL server, and use like 3 tables. In excel only a handful of information arrives and always from Today(), it's narrowed down to load only few columns and rows. This means that in the morning if I open the file it's empty (because it is set to refresh itself upon opening), but during the day it gathers information and at the end of the working day, from those lines it sends out automated emails with the help of a Power Automate flow. Now the file is stored in Sharepoint. The challenge is the I don't want to open the file wait for 10 seconds and close it. I have created a power automate flow which theoretically should refesh the file ( I have included a script in the excel file and that I trigger) but nope, it doesn't work. Some sort of conflict appears. In this way I have to keep in mind to open the excel file every day, and yes you have guessed it also on Saturday, because I won't empty itself and the flow runs every day once. Now I have searched for similar case information, video, something on the internet and I didn't find the solution. Maybe you have a great idea. Love to hear from you back. Thanks. Tibor

[@martinbubenheimer6289]

So, input is SQL Server and output is email notifications. I would replace Excel completely with Power BI, put the semantic model on a frequent refresh schedule and use alerts or data activator in Power BI Service to trigger the emails. Let's see what Bas comes up with.

[@violatorwashere]

@@martinbubenheimer6289 thank you for your thoughts and ideas. I will think about this as well. Tibor

[@DiePic-j1x]

always the best!

[@gnomesukno]

Another super useful video. A+

[@HowtoPowerBI]

thank you!!! 🤓💻

[@Rolly_De_Castro]

Hi, i would like to know what measure did you do in cities lat and lon?

[@HowtoPowerBI]

U can check the pbi file (see link in description)

[@TheStretts]

Another absolute banger

[@HowtoPowerBI]

😎👊 thx for watching again!

[@muhammadbaihaki4301]

Yeah, i like this🔥🔥

[@Abby-g5f6j]

Hi, I want comment somewhat irrelevant thing here, I want know if it is possible (if so, would be great if you make video of it) to plot visual that compares the total of two things (suppose to be X and Y) on a balance (scale) where if the total X is less then Y the scale tilts toward the X side and if Y is less than the scale tilts toward the Y side, and if X and Y are equal the SCALE to be balance with the total of X and Y showing on both sides of the SCALE every time.

[@isamejiv]

Wauuu I love it!!

[@HowtoPowerBI]

awesome !!!! 😁

[@juanes1235]

Wow!

[@ZacharyBoland-d6b]

For some reason, When making the parameter, it doesn't make it a draggable thing like yours, only ranges and tiles, and other stuff. I am unsure why this is

[@maximecoron-dallo1780]

Very cool!

[@HowtoPowerBI]

🚀🚀✌️

[@oriby1]

Hi bass, the file at your website is not the correct one ,it gives me 3 must try visuals.. pls fix 😊

[@HowtoPowerBI]

you timed it exactly when i was adding the section 😁 if you would have tried 1 min later you would have got the right one

[@raeesg]

Does this also work on the Azure Maps visual?

[@HowtoPowerBI]

Yes

[@gauravkosare4201]

I tried to implement the same in Azure Maps, not working for Azure Map Visual. If the any helping document available, please share it.

[@jpfm79]

Bas its possible to do that with streets? distance between streets?

[@tony-ql4wv]

yes, you would just need to adjust the distance formula for law of cosine

[@jpfm79]

@@tony-ql4wv thank you for your answer,but how i do that?

[@tony-ql4wv]

@@jpfm79 some reason my old comment got deleted so this is the formula I used d=acos(sin(lat1)*sin(lat2)+cos(lat1)*cos(lat2)*cos(lng2-lng1))*6371. you just have to plug a,c into d=r*c. the same one Bas provided.

[@jpfm79]

@@tony-ql4wv thank you very much

[@miroslavzacharsky1632]

Is There a way how to calculate distance not based on Haversine distance but real road distance?

[@HowtoPowerBI]

I think not

[@FatherLizard]

You'd likely need more formal GIS tools for that.

[@martinbubenheimer6289]

You could get the road distance between pairs of locations from routing APIs and load them into a table, then pick the distance form the table rather than calculating it on the fly. E.g., Google Maps provides such service: developers.google.com/maps/documentation/distance-matrix

[@juliom6260]

Wow. That one is really trick. Not my level😢