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
Forgive what may be a stupid question, but why are you doing all those merges in Power Query ? I'd normally bring those tables into Power BI and then make relationships between them in the Data Model. Power Query will allways be slow merging data. From a cursory view all those tables can be related in the data model, which will result in a much better, faster model. Why do you need to merge in Power Query ?
Stuart
Thank you for pointing that method out, and I do try to do that when possible. However, in some cases I have more than two columns that need data lookups from the other table... for example, a help ticket has an employee number for Opened By, Owner, Resolved By, and Closed By columns. So four different colunms with employee numbers, and one table to look them up in. With a max of one active relationship and one inactive relationship, I can't make it work to connect those four different colunms to the Employee's key column.
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
For some reason, I thought the max was two relationships, so that's definitely good to learn - thank you! My report already has over 25 measures in it, so I wonder if adding two-dozen more puts the report at risk for slow down on the user's end? Granted, since I'm mainly performing lookups and not calculations/aggregations, it may be wiser for me to take advantage of the 'RELATED' DAX function to draw in the needed column values instead of a measure. My main reason for pushing much of the work to PQ was to keep the report side from becoming too DAX-heavy and affecting user experience.
Measures are only run when they are placed on the canvas, so you can have as many measures as you like but if you only put a few of them on visualisations it won't affect performance. I've certainly seen Power BI reports with hundreds of measures and it doesn't affect load speed or performance. DAX is an efficient language if it's backed by a good Data Model, the key is to use the Star Schema to do the work, then DAX measures can leverage the filter context and only needs to run on a subset of records. Of course it's possible to write inefficient DAX with too much use of iterators which bypass the filter context and churn through all data records, but if you get the Data Model right that shouldn't be necessary.
Stuart