I Need help with a formula to calculate distance from point A to B

[ahmad1111]

Hi so below are just small example of but dont take the cells with from and to and distance as the correct ones but i just posted it as example because i have big data 5000 rows in my excel so what i actually need is i get an order that order will have plant and unloading point code (as my site name thus from all 5000 rows you can get any for example from below point A is lets say A001-422 and point B is A001-422/477 and issue is since data is big i cant just go one site and check with all other 4999 rows to see which city each site belongs to so i need a way for power bi to check when an order comes and excel updates which is my data source and linked to my power bi then power bi should give me the fixed distance that i have in my excel thus distance only linked as city to city based and not site to site based which is what i recieve when order comes so in short order comes as site a to site b and i need to convert that site to its respective city that it belongs to based on the excel i have and then give the distance

Plant	Unloading Point Code	Unloading Point Name	Region	Country/Region Key	City	From Location	To Location	Distance In KM
A001	422	BERRI B-221 GAS PLANT MAINTENANCE	ZEA	SA	BERRI
A001	422/470	BERRI B-221 LIQUID RECOVERY (PLT 470)	ZEA	SA	BERRI		GHAZAL	1,551
A001	422/474	BERRI GAS SWEETINING PLANT	ZEA	SA	BERRI		UTHMANIYAH	227
A001	422/476	BERRI B-1054 UTILITIES PLANT	ZEA	SA	BERRI	BAISH	MAZALIJ	1,234
A001	422/ACS	BERRI B-221, MAINTENANCE A/C SHOP	ZEA	SA	BERRI	BAISH	AL-MUBARRAZ	1,394
A001	422/ELS	BERRI B-221, MAINTENANCE ELECTRICAL SHOP	ZEA	SA	BERRI	BAISH	BIRALGHANEM	968
A001	422/ENG	BERRI B-221, GAS PLANT MAINTEMANCE	ZEA	SA	BERRI	BAISH	KHAMIS MUSHAIT	165
A001	422/F13	BERRI B-F14, RULF. PRILL PLANT F13	ZEA	SA	BERRI	BAISH	MAZALIJ	1,234
A001	422/F14	BERRI B-F14, PLT F14	ZEA	SA	BERRI
A001	422/F18	BERRI B-1054, F18 COGENERATION	ZEA	SA	BERRI
A001	422/F20	BERRI B-1054, ETHANE RECOVERY	ZEA	SA	BERRI
A001	422/IED	BERRI B-201, INSTRUMENT & DIG SHOP	ZEA	SA	BERRI
A001	422/MAS	BERRI B-201, ROTATING EQUIPMENT SHOP	ZEA	SA	BERRI
A001	422/MCU	BERRI B-210, BGP MATL. COORDINATION UNIT	ZEA	SA	BERRI
A001	422/MES	BERRI B-1053, MAINTENANCE METALS SHOP	ZEA	SA	BERRI
A001	422/PIU	BERRI B-1054, USER PICK-UP	ZEA	SA	BERRI
A001	422/R64	BERRI B-471 SULFUR PLANT,ALLTRAIN PLT471	ZEA	SA	BERRI
A001	422/R68	BERRI B-221, OFF-SITE ARE	ZEA	SA	BERRI
A001	422/R69	BERRI B-475, ETHANE PLANT PLT 475	ZEA	SA	BERRI
A001	422/RVS	BERRI B-201 RVS AT BERRI G. PLANT MAINT.	ZEA	SA	BERRI
A001	422/T&I	BERRI B-221, T&I YARD	ZEA	SA	BERRI
A001	C300	RASTANURA STORES	ZEA	SA	RAS TANURA
A001	C321	JUAYMAH B-321, STORES	ZEA	SA	JUAYMAH
A001	C422	BERRI GAS PLANT MAIN	ZEA	SA	BERRI
A001	H01A	JUAYMAH MSSD REVERSE ENGINEERING CENTER	ZEA	SA	JUAYMAH
A002	531	SHEDGUM STORES UNIT	ZEA	SA	SHEDGUM
A002	532/TXI	UTHMANIYAH  GAS PLANT TAXI MATERIALS	ZEA	SA	UTHMANIYAH
A002	566	UTHMANIYAH  NGL FIRE CONTROL UNIT	ZEA	SA	UTHMANIYAH

[bhanu_gautam]

@ahmad1111 Create a table that maps each site to its respective city. This table will help you convert site codes to city names.

Create relationships between your main data table and the mapping table based on the site codes

Create calculated columns in your main data table to convert site codes to city names using DAX.

From City = RELATED('MappingTable'[City])
To City = RELATED('MappingTable'[City])

Create a measure to calculate the distance between cities. Assuming you have a table with distances between cities, you can use a DAX

Distance =
VAR FromCity = [From City]
VAR ToCity = [To City]
RETURN
CALCULATE(
MAX('DistanceTable'[Distance In KM]),
'DistanceTable'[From Location] = FromCity,
'DistanceTable'[To Location] = ToCity
)

[ahmad1111]

@bhanu_gautam thanks for your reply i got bit confused little bit is it possible to give me in steps mainly the first steps regarding creating table and relationships as i got lost with the mapping table and main data table as in do you mean main table is my excel with the sites and cities and distance and mapping table is a new table i create in power bi but if so then what do you put in it or do with it within power bi so steps will be very helpful thanks