Multiple Fact Table Memory Issues

[michaelsp]

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 dimension tables for location where the location 2 of leg1 is location1 of leg2 and location2 of leg2 is location1 of leg3.  

I can then create a table visual with the location from the 4 dimension tables as well as the average of a column in the fact tables. I would also like to add a column which is the sum of those averages.  Normally I would just create a measure average(leg1[X])+average(leg2[X])+average(leg3[x]), but this runs out of memory.  

It seems the issue is that when you add the average of a column into a table it is calculated diferently from a measure and that causes the memory overflow.

Is there some way to force each average to be done only within the fact table?

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[michaelsp]

Here is simplified version of the data.  The actual data for leg[123] has tens of thousands of rows.

leg1

location1	location2	X
1	2	1
1	3	2
2	3	3
1	2	4
2	1	5

leg2

location1	location2	X
2	1	1
2	3	2
3	2	3
1	3	4
2	3	5

leg3

location1	location2	X
1	2	1
2	3	2
3	1	3
3	2	4
2	1	5

location[ABCD]

location	name
1	q
2	r
3	s

Expected Results

Location A	Location B	Location C	Location D	Avg X leg1	Avg X leg2	Avg X leg3	total
q	r	q	r	2.5	1	1	4.5
q	r	s	q	2.5	3.5	3	9
q	r	s	r	2.5	3.5	4	10
q	s	r	q	2	3	5	10
q	s	r	s	2	3	2	7
r	q	s	q	5	4	3	12
r	q	s	r	5	4	4	13
r	s	r	q	3	3	5	11
r	s	r	s	3	3	2	8

[lbendlin]

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

[michaelsp]

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]

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]

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]

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

Check how this behaves at scale.

[michaelsp]

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.

[lbendlin]

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)