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 us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - January 2025
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
Fabric Community Update - January 2025
Find out what's new and trending in the Fabric community.
