Forum Discussion
Modeling problem in Logistic industry
Dear all,
I have a problem with my modeling.
Here is Dim Table
This one is Fact Table
The structure of data is: Flight departs from HAN and arrives at SGN carries cargo from HAN/SGN to CDG
Relationship between 2 tables I have to use is
1. Ori Awb (Fact Table) to Airport (dim Table)
2. Des Awb (Fact Table) to Airport (dim Table)
3. From and to of Sector. Eg Sector HAN-CDG departs from HAN airport and arrived at CDG airport. This pair of Ori-Des of Sector also has relationship with dim Table.
Results should be as follow:
How can I get these results if I just want to use one dim table? If I keep this model in excel workbook is there any disadvantage than in Power BI?
Thanks a lot.
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.
2 Replies
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.
Thanks a lot for your solution. It brings new method of approach to me.
