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