PowerBI - Difference between two rows combined with multi-table filters

1
votes

I am new to powerbi DAX and struggling to compute difference between two lines.

The dataset describes a bus network and is composed of three tables. Example given below:

Table1

Transport_type, Line_number
Bus, 101
Bus, 102

Table2

Line_number, Trip, Stop_name, Stop_sequence
101, monday-101-1, stop-101-a, 1
101, monday-101-1, stop-101-b, 2
101, monday-101-1, stop-101-c, 3
101, monday-101-2, stop-101-a, 1
101, monday-101-2, stop-101-c, 3
101, monday-101-2, stop-101-b, 2
102, monday-102-1, stop-102-c, 3
102, monday-102-1, stop-102-b, 2
102, monday-102-1, stop-102-a, 1
102, monday-102-2, stop-102-a, 1
102, monday-102-2, stop-102-b, 2
102, monday-102-2, stop-102-c, 3

Table3

Stop_name, Altitude
stop-101-a, 540
stop-101-b, 560
stop-101-c, 590
stop-102-a, 230
stop-102-b, 210
stop-102-c, 170

Relationships between tables is already set in the model tab; and in the report tab i created a table and added all parameter through drag-drop.

With Table3 selected, I create a new calculated column and desperately try to compute altitude difference between each stops of the trip.

Example of expected result

Transport_type, Line_number, Trip, Stop_name, Stop_sequence, Altitude difference
Bus, 101, monday-101-1, stop-101-a, 1, 0
Bus, 101, monday-101-1, stop-101-b, 2, 20 (=560-540)
Bus, 101, monday-101-1, stop-101-c, 3, 30 (=590-560)
Bus, 101, monday-101-2, stop-101-a, 1, 0
Bus, 101, monday-101-2, stop-101-c, 3, 30 (=590-560)
Bus, 101, monday-101-2, stop-101-b, 2, 20 (=560-540)
Bus, 102, monday-102-1, stop-102-c, 3, -40 (=170-210)
Bus, 102, monday-102-1, stop-102-b, 2, -20 (=210-230)
Bus, 102, monday-102-1, stop-102-a, 1, 0
Bus, 102, monday-102-2, stop-102-a, 1, 0
Bus, 102, monday-102-2, stop-102-b, 2, -20 (=210-230)
Bus, 102, monday-102-2, stop-102-c, 3, -40 (=170-210)

Does anyone have any idea on how to combine filters from different tables to then subtract two rows ?

Thanks for your help :)

2
filterpowerbidifference
Can you share the measure/column code you are trying with? – mkRabbani
I am trying to use the code i found on a "similar" question: stackoverflow.com/questions/57710425/… – CharlesAntoine

2 Answers

0
votes

You can perform some transformation in power query to achieve your required output. I have taken table2 and Table3 in consideration. Here below the Advanced Query for Table2-

Important: Stop Sequence played the major role here for finding the starting and ending for sopt_name.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciotVtJRMjQwBJK5+XkpiZW6QI4uiFtckl8A5iSCVCjF6hCnOgnIMSJadTKQY4xPtRFJLjEi32xMdxshqzZCdrcRFrPxqSbNbExfoqk2Il81QZcYYfFlLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Transport_type = _t, Line_number = _t, Trip = _t, Stop_name = _t, Stop_sequence = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Transport_type", type text}, {"Line_number", Int64.Type}, {"Trip", type text}, {"Stop_name", type text}, {"Stop_sequence", Int64.Type}}),

    //-- My transformation starts from here
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Line_number", Order.Ascending}, {"Trip", Order.Ascending}, {"Stop_sequence", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", each [Stop_sequence] - 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Line_number", "Trip", "Custom"}, #"Added Custom", {"Line_number", "Trip", "Stop_sequence"}, "Added Custom", JoinKind.LeftOuter),
    #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Stop_name"}, {"Added Custom.Stop_name"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Added Custom",{{"Added Custom.Stop_name", "stop_name_start"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Transport_type", "Line_number", "Trip", "stop_name_start", "Stop_name", "Stop_sequence", "Custom"}),
    #"Merged Queries1" = Table.NestedJoin(#"Reordered Columns", {"stop_name_start"}, table3, {"Stop_name"}, "table3", JoinKind.LeftOuter),
    #"Expanded table3" = Table.ExpandTableColumn(#"Merged Queries1", "table3", {"Altitude"}, {"table3.Altitude"}),
    #"Merged Queries2" = Table.NestedJoin(#"Expanded table3", {"Stop_name"}, table3, {"Stop_name"}, "table3", JoinKind.LeftOuter),
    #"Expanded table1" = Table.ExpandTableColumn(#"Merged Queries2", "table3", {"Altitude"}, {"table3.Altitude.1"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded table1",{{"table3.Altitude", "start_altitude"}, {"table3.Altitude.1", "stop_altitude"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns1", "Custom.1", each [stop_altitude] - [start_altitude]),
    #"Renamed Columns2" = Table.RenameColumns(#"Added Custom1",{{"Custom.1", "distance"}})
in
    #"Renamed Columns2"

Here is the final output-

enter image description here

0
votes

Thank you very much for your help !

I am trying to replicate your solution, but having an error at the first merged query:

let
    Source = Csv.Document(File.Contents("C:\Users\l.charles-antoine\Desktop\Test BI\gtfs_fp2020_2020-08-26\test alt-dist\stop_tumes.csv"),[Delimiter=";", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Line_number", Int64.Type}, {" Trip", type text}, {" Stop_name", type text}, {" Stop_sequence", Int64.Type}}),

    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Line_number", Order.Ascending}, {" Trip", Order.Ascending}, {" Stop_sequence", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", each ([#" Stop_sequence"])-1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Line_number", "Trip", "Custom"}, #"Added Custom", {"Line_number", "Trip", "Stop_sequence"}, "Added Custom", JoinKind.LeftOuter)
in
    #"Merged Queries"

Error is : Expression.Error: The column 'Trip' of the table wasn't found. Details: Trip