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...
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.

 

 

 

  • michaelsp's avatar
    michaelsp
    Frequent Visitor
    2 years ago

    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's avatar
    lbendlin
    Super User
    2 years ago

    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)