Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Magpie_Rob
Helper I
Helper I

Distance Bins based on long and lat

‎02-12-2021 02:58 PM

Hello

 

So I have 2 tables, on with customers and there Longitude and Latitude and anotherone with Stores with there longitude and latitude - I'm trying to create a table with a list of the stores and how many customers fall withing certain distance (in bins). (5miles, 10miles, 15 miles) etc. I have included an excel table of how I would expect it to look.

Distance Bins.png

 

I found a measure created by RADCAD which figures out the distance but I can't figure out how to make this work across mutilple stores and count it plus put it into bins. any help is greatly appricated. - I included a sample Pbix as well. 

 

https://1drv.ms/u/s!ApUZenl1efyn03AHr5mVd-BZI1Tk?e=Wgos2m

 

Thanks 

Magpie

 

Labels:
Message 1 of 10
1,101 Views
0
2 ACCEPTED SOLUTIONS
parry2k
Super User
Super User

‎02-12-2021 03:08 PM

@Magpie_Rob is this what you are looking for:

 

parry2k_0-1613171309698.png

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

Message 2 of 10
1,072 Views
Magpie_Rob
Helper I
Helper I
In response to parry2k

‎03-05-2021 04:34 AM

This was the main bit! it worked really well thank you! 

Store Distance Bins = 
ADDCOLUMNS (
    CROSSJOIN (
        VALUES ( Store_Location[Postcode] ),
        VALUES ( 'Customers Locations'[id] )
    ),
    "Miles", [Miles],
)

 

View solution in original post

Message 5 of 10
836 Views
0
9 REPLIES 9
parry2k
Super User
Super User

‎03-01-2021 02:02 PM

@Magpie_Rob I'm so so sorry about this. I totally messed up. Thanks for keep on following up. Filesize is bigger than the size I can attach here, let me know how else I can send it to you.

 

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Message 9 of 10
866 Views
0
Magpie_Rob
Helper I
Helper I
In response to parry2k

‎03-03-2021 01:34 AM

Hi @parry2k  no worries at all! everyone is busy! I have sent you a direct message with my email

Message 10 of 10
850 Views
0
parry2k
Super User
Super User

‎02-17-2021 03:10 PM

@Magpie_Rob oh! I'm so sorry for not getting back to you. Thanks for the nudge. I will send you the pbix file as soon as I'm in front of the computer.

 

Stay tuned!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Message 6 of 10
931 Views
0
Magpie_Rob
Helper I
Helper I
In response to parry2k

‎03-01-2021 12:42 PM

Hi @parry2k I hope you are well, at the risk of sounding really desperate could you please share the Pbix with me please ? 

Thank you! 

Message 8 of 10
885 Views
0
Magpie_Rob
Helper I
Helper I
In response to parry2k

‎02-24-2021 02:59 AM

Hi @parry2k I hope you are well,

I fear you may have forgotten me again 🙈 at the risk of sounding desperate could you please share the Pbix with me please ? 

Thank you! 

Message 7 of 10
905 Views
0
parry2k
Super User
Super User

‎02-12-2021 03:08 PM

@Magpie_Rob is this what you are looking for:

 

parry2k_0-1613171309698.png

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Message 2 of 10
1,073 Views
Magpie_Rob
Helper I
Helper I
In response to parry2k

‎03-05-2021 04:34 AM

This was the main bit! it worked really well thank you! 

Store Distance Bins = 
ADDCOLUMNS (
    CROSSJOIN (
        VALUES ( Store_Location[Postcode] ),
        VALUES ( 'Customers Locations'[id] )
    ),
    "Miles", [Miles],
)

 

Message 5 of 10
837 Views
0
Magpie_Rob
Helper I
Helper I
In response to parry2k

‎02-13-2021 08:24 AM

@parry2k yeah thats exactly what I'm looking for! are you able to share the pbix or let me know how you achived it? 

Message 3 of 10
1,025 Views
0
Magpie_Rob
Helper I
Helper I
In response to Magpie_Rob

‎02-17-2021 02:56 PM

Hi @parry2k just checking you haven't forgot about me! the soultion to this would be very much appricated! 

 

Thanks 

Message 4 of 10
932 Views
0

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

View All
Users online (2,936)