Forum Discussion

michaelsp's avatar
michaelsp
Frequent Visitor
2 years ago

Multiple Fact Table Memory Issues

Hi,   I have 3 fact tables (leg1,leg2,leg3) each of which have a location1 and location2 as well as some other values that I have in slicers.   I connect those tables together using 4 identical d...
lbendlin's avatar
lbendlin
Super User
2 years ago

Shouldn't the columns in leg2 be location2 and location3  ?

michaelsp's avatar
michaelsp
Frequent Visitor
2 years ago

I guess thats a better naming convention.  In my original data the leg tables all have the same format so I carried that over.

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    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

    • michaelsp's avatar
      michaelsp
      Frequent Visitor
      2 years ago

      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.

      • lbendlin's avatar
        lbendlin
        Super User
        2 years ago

        Here is a certifiably insane data model that seems to be doing what you need

        Check how this behaves at scale.