1 ACCEPTED SOLUTION
Hi @Joaomatos2002,
Comment: are you sure that you have correct distance calculated in your sample?
DAX solution (power bi files attached. First as a simple measure, second with calculated columns as helper)
Output
Create as a new measure:
Distance KM =
VAR _IDCard = SELECTEDVALUE(Table1[ID_CARD])
VAR _endTime = SELECTEDVALUE(Table1[ENDTIME])
VAR _lat = SELECTEDVALUE(Table1[latitude])
VAR _lon = SELECTEDVALUE(Table1[longitude])
VAR _PI_D180 = PI()/180
VAR _tbl =
TOPN(
1,
FILTER(
ALL(Table1),
Table1[ID_CARD] = _IDCard &&
Table1[ENDTIME] < _endTime
),
[ENDTIME],
DESC
)
VAR _latPrev =
COALESCE(
CALCULATE(
MIN(Table1[latitude]),
_tbl
),
_lat
)
VAR _lonPrev =
COALESCE(
CALCULATE(
MIN(Table1[longitude]),
_tbl
),
_lon
)
VAR _result = ACOS(SIN(_lat*_PI_D180)*SIN(_latPrev*_PI_D180)+COS(_lat*_PI_D180)*COS(_latPrev*_PI_D180)*COS((_lonPrev*_PI_D180)-(_lon*_PI_D180)))*6371
VAR _result2 = IF(ISINSCOPE(Table1[ID_CARD]), _result)
RETURN _result2
Power Query solution:
Output:
let
fnShift = (tbl as table, col as text, shift as nullable number, optional newColName as text, optional _type as type) as table =>
//v 3. parametri zadaj zaporne cislo ak chces posunut riadky hore, kladne ak dole, 4. je nepovinny (novy nazov stlpca), 5. je nepovinny typ
let
a = Table.Column(tbl, col),
b = if shift = 0 or shift = null then a else if shift > 0
then List.Repeat({null}, shift) & List.RemoveLastN(a, shift)
else List.RemoveFirstN(a, shift * -1) & List.Repeat({null}, shift * -1),
c = Table.FromColumns(Table.ToColumns(tbl) & {b}, Table.ColumnNames(tbl) &
( if newColName <> null then {newColName} else
if shift = 0 then {col & "_Duplicate"} else
if shift > 0 then {col & "_PrevValue"}
else {col & "_NextValue"} )),
d = Table.TransformColumnTypes(c, {List.Last(Table.ColumnNames(c)), if _type <> null then _type else type any})
in
d,
fnDistance =
(lat1 as number, lon1 as number, lat2 as number, lon2 as number, optional km_or_mi as text)=>
let
lat1_rad = lat1/ 180 * Number.PI,
lon1_rad = lon1 / 180 * Number.PI,
lat2_rad = lat2/ 180 * Number.PI,
lon2_rad = lon2 / 180 * Number.PI,
unit = if km_or_mi = "mi" then 3959 else 6371, //Earth radius 3959 for miles, 6371 for kilometers
distance = Number.Acos(Number.Sin(lat1_rad) * Number.Sin(lat2_rad) + Number.Cos(lat1_rad) * Number.Cos(lat2_rad) * Number.Cos(lon2_rad-lon1_rad)) * unit
in
distance,
fnCalc =
(myTable as table)=>
[
// _Detail = GroupedRows{[ID_CARD="A"]}[fnCalc],
_Detail = myTable,
_SortedRows = Table.Sort(_Detail, {{"ENDTIME", Order.Ascending}}),
_Ad_LatLong_PrevValues = fnShift(fnShift(_SortedRows, "latitude", 1, null, type number), "longitude", 1, null, type number),
_Ad_Distance = Table.AddColumn(_Ad_LatLong_PrevValues, "distanceKM", each fnDistance([latitude], [longitude], [latitude_PrevValue]? ?? [latitude], [longitude_PrevValue]? ?? [longitude], "km"), type number),
_RemovedColumns = Table.RemoveColumns(_Ad_Distance, {"latitude_PrevValue", "longitude_PrevValue"})
][_RemovedColumns],
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdRLjsMgEATQq4y8TiZU82c3cwpLke9/jQEmUtmQNF5YuCz1E79+Pref7bbt9blfXztC+4H6Ah4mPcSIu3x8GVs8hsgVIzVy+DbtqcN7eg2Pm4K1XDSrFo5ThEgLtKBajdmtbkmeIp9pCS1RrcbsTrNCwWiF4hwtS8uqlltasdhxDVOBoeVouW79frB8y4Nu+TFKxSdanpZX5xWWVno3L3eaV6AVVKuV2aNu9cLnKBfnaUVaUV3DthZ9IT9ZSMWEKbKn+5VoJdXKPdcm1irbKfKBWCaWVayfqBXmhx1DLpanHuwcMDrWW0fWsFrZT9HpOoOtA9AxWfUpMcVgigTE2DsgOmZXjapVHvZM8H8XXhibB/Tmgd49ko6NeyZSDG8Z2D3gdMyv9qxVHmcmlwPC9lGHx/EH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID_CARD = _t, ID_ACTIVITY = _t, ID_LOCATION = _t, CALDAY = _t, STARTTIME = _t, ENDTIME = _t, latitude = _t, longitude = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"ID_CARD", type text}, {"ID_ACTIVITY", type text}, {"ID_LOCATION", type text}, {"CALDAY", type date}, {"STARTTIME", type datetime}, {"ENDTIME", type datetime}}),
ChangedTypeUS = Table.TransformColumnTypes(ChangedType,{{"latitude", type number}, {"longitude", type number}}, "en-US"),
GroupedRows = Table.Group(ChangedTypeUS, {"ID_CARD"}, {{"fnCalc", fnCalc, type table}}),
Combined = Table.Combine(GroupedRows[fnCalc])
in
Combined
Hi @Joaomatos2002,
Comment: are you sure that you have correct distance calculated in your sample?
DAX solution (power bi files attached. First as a simple measure, second with calculated columns as helper)
Output
Create as a new measure:
Distance KM =
VAR _IDCard = SELECTEDVALUE(Table1[ID_CARD])
VAR _endTime = SELECTEDVALUE(Table1[ENDTIME])
VAR _lat = SELECTEDVALUE(Table1[latitude])
VAR _lon = SELECTEDVALUE(Table1[longitude])
VAR _PI_D180 = PI()/180
VAR _tbl =
TOPN(
1,
FILTER(
ALL(Table1),
Table1[ID_CARD] = _IDCard &&
Table1[ENDTIME] < _endTime
),
[ENDTIME],
DESC
)
VAR _latPrev =
COALESCE(
CALCULATE(
MIN(Table1[latitude]),
_tbl
),
_lat
)
VAR _lonPrev =
COALESCE(
CALCULATE(
MIN(Table1[longitude]),
_tbl
),
_lon
)
VAR _result = ACOS(SIN(_lat*_PI_D180)*SIN(_latPrev*_PI_D180)+COS(_lat*_PI_D180)*COS(_latPrev*_PI_D180)*COS((_lonPrev*_PI_D180)-(_lon*_PI_D180)))*6371
VAR _result2 = IF(ISINSCOPE(Table1[ID_CARD]), _result)
RETURN _result2
Power Query solution:
Output:
let
fnShift = (tbl as table, col as text, shift as nullable number, optional newColName as text, optional _type as type) as table =>
//v 3. parametri zadaj zaporne cislo ak chces posunut riadky hore, kladne ak dole, 4. je nepovinny (novy nazov stlpca), 5. je nepovinny typ
let
a = Table.Column(tbl, col),
b = if shift = 0 or shift = null then a else if shift > 0
then List.Repeat({null}, shift) & List.RemoveLastN(a, shift)
else List.RemoveFirstN(a, shift * -1) & List.Repeat({null}, shift * -1),
c = Table.FromColumns(Table.ToColumns(tbl) & {b}, Table.ColumnNames(tbl) &
( if newColName <> null then {newColName} else
if shift = 0 then {col & "_Duplicate"} else
if shift > 0 then {col & "_PrevValue"}
else {col & "_NextValue"} )),
d = Table.TransformColumnTypes(c, {List.Last(Table.ColumnNames(c)), if _type <> null then _type else type any})
in
d,
fnDistance =
(lat1 as number, lon1 as number, lat2 as number, lon2 as number, optional km_or_mi as text)=>
let
lat1_rad = lat1/ 180 * Number.PI,
lon1_rad = lon1 / 180 * Number.PI,
lat2_rad = lat2/ 180 * Number.PI,
lon2_rad = lon2 / 180 * Number.PI,
unit = if km_or_mi = "mi" then 3959 else 6371, //Earth radius 3959 for miles, 6371 for kilometers
distance = Number.Acos(Number.Sin(lat1_rad) * Number.Sin(lat2_rad) + Number.Cos(lat1_rad) * Number.Cos(lat2_rad) * Number.Cos(lon2_rad-lon1_rad)) * unit
in
distance,
fnCalc =
(myTable as table)=>
[
// _Detail = GroupedRows{[ID_CARD="A"]}[fnCalc],
_Detail = myTable,
_SortedRows = Table.Sort(_Detail, {{"ENDTIME", Order.Ascending}}),
_Ad_LatLong_PrevValues = fnShift(fnShift(_SortedRows, "latitude", 1, null, type number), "longitude", 1, null, type number),
_Ad_Distance = Table.AddColumn(_Ad_LatLong_PrevValues, "distanceKM", each fnDistance([latitude], [longitude], [latitude_PrevValue]? ?? [latitude], [longitude_PrevValue]? ?? [longitude], "km"), type number),
_RemovedColumns = Table.RemoveColumns(_Ad_Distance, {"latitude_PrevValue", "longitude_PrevValue"})
][_RemovedColumns],
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdRLjsMgEATQq4y8TiZU82c3cwpLke9/jQEmUtmQNF5YuCz1E79+Pref7bbt9blfXztC+4H6Ah4mPcSIu3x8GVs8hsgVIzVy+DbtqcN7eg2Pm4K1XDSrFo5ThEgLtKBajdmtbkmeIp9pCS1RrcbsTrNCwWiF4hwtS8uqlltasdhxDVOBoeVouW79frB8y4Nu+TFKxSdanpZX5xWWVno3L3eaV6AVVKuV2aNu9cLnKBfnaUVaUV3DthZ9IT9ZSMWEKbKn+5VoJdXKPdcm1irbKfKBWCaWVayfqBXmhx1DLpanHuwcMDrWW0fWsFrZT9HpOoOtA9AxWfUpMcVgigTE2DsgOmZXjapVHvZM8H8XXhibB/Tmgd49ko6NeyZSDG8Z2D3gdMyv9qxVHmcmlwPC9lGHx/EH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID_CARD = _t, ID_ACTIVITY = _t, ID_LOCATION = _t, CALDAY = _t, STARTTIME = _t, ENDTIME = _t, latitude = _t, longitude = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"ID_CARD", type text}, {"ID_ACTIVITY", type text}, {"ID_LOCATION", type text}, {"CALDAY", type date}, {"STARTTIME", type datetime}, {"ENDTIME", type datetime}}),
ChangedTypeUS = Table.TransformColumnTypes(ChangedType,{{"latitude", type number}, {"longitude", type number}}, "en-US"),
GroupedRows = Table.Group(ChangedTypeUS, {"ID_CARD"}, {{"fnCalc", fnCalc, type table}}),
Combined = Table.Combine(GroupedRows[fnCalc])
in
Combined
Helpful resources
Join our Fabric User Panel
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Power BI Monthly Update - June 2025
Check out the June 2025 Power BI update to learn about new features.
Fabric Community Update - June 2025
Find out what's new and trending in the Fabric community.
