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
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
Again, thanks Stuart. I'd thought perhaps the measures were "pre-calculated" as the model loads, so knowing there's no resources "cost" until they're put into visuals helps me feel more comfortable with creating more of them. Despite my egregious use of merges, the remainder of my model does resemble a star schema, and I definitely try to write efficient DAX (though I wish it were easier to recognize an improvement to the syntax). I really appreciate your guidance on this!