Forum Discussion
Slow Power Query & refresh performance w/ multiple merges/lookups possible cause
Whilst you can have only one active relationship in a table, you can have any number of inactive relationships (dotted lines) between tables. I'd create the inactive relationships in the model then use the DAX USERELATIONSHIP on measures to swap between them.
Stuart
I'm going to take a different approach. You need those merges to build a proper model as I understand it. I would not bring those tables in separately then create filter relationships.
However, Power Query will bog down a server with multiple merges. PQ will try to create the SQL and it doesn't always do it as effeciently as a SQL coder can do in SSMS. Yes, Table.Buffer can help, but if the data is large, then it can hurt more than it helps.
My recommendation is create a view on the server where the merges take place there, then bring that view into PQ. You can try to bring them all in separately and use LOOKUPVALUE and other measures, to model your data in DAX, but your overall performance in the report will be worse than if the merges had been done at the source, and your DAX will sure as heck be more complex to write and maintain.