Forum Discussion
Multiple Fact Table Memory Issues
Doesn't really matter. Here is the general approach:
let
Source = leg1,
#"Grouped Rows" = Table.Group(Source, {"locationA", "locationB"}, {{"X1", each List.Average([X1]), type nullable number}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", (k)=> Table.SelectRows(leg2, each [locationB]=k[locationB])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"locationC", "X2"}, {"locationC", "X2"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Custom",{"locationA", "locationB", "locationC", "X1", "X2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"locationC", Int64.Type}, {"X2", Int64.Type}}),
#"Grouped Rows1" = Table.Group(#"Changed Type", {"locationA", "locationB", "locationC"}, {{"X1", each List.Max([X1]), type nullable number}, {"X2", each List.Average([X2]), type nullable number}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Custom", (k)=> Table.SelectRows(leg3, each [locationC]=k[locationC])),
#"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"locationD", "X3"}, {"locationD", "X3"}),
#"Reordered Columns1" = Table.ReorderColumns(#"Expanded Custom1",{"locationA", "locationB", "locationC", "locationD", "X1", "X2", "X3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns1",{{"locationD", Int64.Type}, {"X3", Int64.Type}}),
#"Grouped Rows2" = Table.Group(#"Changed Type1", {"locationA", "locationB", "locationC", "locationD"}, {{"X1", each List.Max([X1]), type nullable number}, {"X2", each List.Max([X2]), type nullable number}, {"X3", each List.Average([X3]), type nullable number}}),
#"Added Custom2" = Table.AddColumn(#"Grouped Rows2", "total", each [X1]+[X2]+[X3],type number)
in
#"Added Custom2"
You can paint "Go Faster" stripes on it by using Table.Buffer for each of the tables.
let
Source = Table.Buffer(leg1),
#"Grouped Rows" = Table.Group(Source, {"locationA", "locationB"}, {{"X1", each List.Average([X1]), type nullable number}}),
Source2 = Table.Buffer(leg2),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", (k)=> Table.SelectRows(Source2, each [locationB]=k[locationB])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"locationC", "X2"}, {"locationC", "X2"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Custom",{"locationA", "locationB", "locationC", "X1", "X2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"locationC", Int64.Type}, {"X2", Int64.Type}}),
#"Grouped Rows1" = Table.Group(#"Changed Type", {"locationA", "locationB", "locationC"}, {{"X1", each List.Max([X1]), type nullable number}, {"X2", each List.Average([X2]), type nullable number}}),
Source3 = Table.Buffer(leg3),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Custom", (k)=> Table.SelectRows(Source3, each [locationC]=k[locationC])),
#"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"locationD", "X3"}, {"locationD", "X3"}),
#"Reordered Columns1" = Table.ReorderColumns(#"Expanded Custom1",{"locationA", "locationB", "locationC", "locationD", "X1", "X2", "X3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns1",{{"locationD", Int64.Type}, {"X3", Int64.Type}}),
#"Grouped Rows2" = Table.Group(#"Changed Type1", {"locationA", "locationB", "locationC", "locationD"}, {{"X1", each List.Max([X1]), type nullable number}, {"X2", each List.Max([X2]), type nullable number}, {"X3", each List.Average([X3]), type nullable number}}),
#"Added Custom2" = Table.AddColumn(#"Grouped Rows2", "total", each [X1]+[X2]+[X3],type number)
in
#"Added Custom2"You could also experiment with Table.AddKey for the join columns.
See attached
Thank you.
That appears to just be a single large table that groups all the existing tables. I was hoping to avoid that and have it just as a measure. When i add the X columns into a table visual it has no trouble calculating average for each of the legs independently, but when I try to create a measure that is the sum of those averages it runs out of memory.
Here is a certifiably insane data model that seems to be doing what you need
Check how this behaves at scale.
Is that different than using the dimension tables to perform the joins? It didnt seem to work and ultimately this will be in the report server version which I dont think allows many to many.
Is there a way to trick power bi into doing each of the averages inside the leg tables and then just add them at the end? Since each average is only within the table and doesnt rely on the other tables. Using variables or removing context somehow?
Thanks for all your help with this.
You never mentioned Report Server. I have no experience in that, and no way to test. I hope someone else can help you further.
here is a calculated table that may work on Report Server, but it is not dynamic.
Table = var l1=SELECTCOLUMNS(leg1,"locationA",[locationA]&"","locationB",[locationB]&"","X1",[X1]) var l2=SELECTCOLUMNS(leg2,"locationB",[locationB]&"","locationC",[locationC]&"","X2",[X2]) var l3=SELECTCOLUMNS(leg3,"locationC",[locationC]&"","locationD",[locationD]&"","X3",[X3]) return NATURALINNERJOIN(NATURALINNERJOIN(l1,l2),l3)