Modeling problem in Logistic industry

Hi Mai_Tran ,

According to your description, here are my steps you can follow as a solution.

(1) Copy a fact table, open "Advanced Editor" in power query, copy and paste the following code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nD003V2cVfSAbGAJIRtZGCgFKuDLBvsjpA1RpL18QhC02uCQxbCNgXJxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sector Airport" = _t, #"Ori AwB" = _t, #"Dest AwB" = _t, Weight = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sector Airport", type text}, {"Ori AwB", type text}, {"Dest AwB", type text}, {"Weight", Int64.Type}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Sector Airport", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"Sector Airport.1", "Sector Airport.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Sector Airport.1", type text}, {"Sector Airport.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Ori AwB", "Dest AwB"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Type", each "Sector"),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Sector Airport.1", "Org"}, {"Sector Airport.2", "Des"}}),
    Custom1 = Table.RemoveColumns(#"Changed Type1",{"Sector Airport.1", "Sector Airport.2"}),
    #"Added Custom1" = Table.AddColumn(Custom1, "Type", each "Awb"),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Custom1",{{"Ori AwB", "Org"}, {"Dest AwB", "Des"}}),
    #"Appended Query" = Table.Combine({#"Renamed Columns1", #"Renamed Columns"})
in
    #"Appended Query"

(2) We can create tables.

from = var a=SUMMARIZE('FactTable (2)',[Type])
return GENERATE(a,DimTable)

to = var a=SUMMARIZE('FactTable (2)',[Type])
return GENERATE(a,DimTable)

Type = SUMMARIZE('FactTable (2)',[Type])

(3) We can create a measure.

Measure = CALCULATE(SUM('FactTable (2)'[Weight]),'FactTable (2)'[Org] in VALUES('from'[Airport]),'FactTable (2)'[Des] in VALUES('to'[Airport]))

(4) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.