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

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)