Forum Discussion
Visual initial loading time retrieval
Hi CU08101843,
The fact that the visuals load in about 3 seconds when the relationship is disabled is a very strong clue.
I would not focus on the 9 MB PBIX size here. In DirectQuery, the important part is the SQL Power BI generates against the source.
With the relationship active, Power BI may need to join the two tables for each visual, and that join can become expensive depending on:
relationship cardinality join key data type indexes filter direction source table sizeMicrosoft’s DirectQuery guidance specifically recommends checking the generated source query and optimizing the relationship/join columns.
I would start with Performance Analyzer and copy the query for one of the slow visuals.
Then run that query directly against the database and check:
- execution plan
- indexes on both relationship keys
- whether the join columns use the same data type
- whether casts or calculated expressions are being introduced
- whether the relationship is bidirectional unnecessarily
One setting worth testing is Assume referential integrity.Microsoft documents that, when the data satisfies the requirements, Power BI can use an INNER JOIN instead of an OUTER JOIN, which can make DirectQuery joins more efficient.
The requirements are important though:
- no null/blank values on the many-side key
- every many-side key must have a matching value on the one side
Microsoft explains that behavior in the Assume referential integrity documentation.So my troubleshooting order would be:
Performance Analyzer -> inspect generated SQL -> validate indexes and join keys -> test Assume referential integrity if the data allows it -> review relationship direction/cardinalityIf the same SQL query still takes close to a minute when run directly against the database, then the main fix will be at the source/index/query-plan level rather than in DAX.
Getting every DirectQuery visual under 2 seconds may not always be realistic, but a jump from 3 seconds to 60 seconds because of one relationship is definitely something I would investigate at the generated join first.
AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
Solution SageHi CU08101843,
I think the key point is that your test already tells you where the problem is. With the relationship inactive the visuals load in 3 seconds, so the cost comes from the join that DirectQuery generates when the relationship is active, not from the measures.
In DirectQuery every visual sends SQL to the source, and an active relationship turns into a join in that SQL. If Power BI cannot assume the keys always match, it uses outer joins, which are much heavier on a large table. You do not need to touch the DAX or the queries to improve that. The relationship setting is described in the Assume referential integrity documentation
The setting only helps if it is true, meaning every row on the many side has a matching key on the one side. It is also worth checking that both tables come from the same source, because a relationship across two different sources cannot be pushed down as a single join. To see the SQL that is sent, run Performance Analyzer and look at the query for the slow visuals, as described in the Performance Analyzer documentation
So I would work through it in this order
1. Performance Analyzer -> copy the query of the slow visual
2. Relationship -> Assume referential integrity (if the data allows it)
3. Source database -> index the join key columns
4. Both tables in the same source -> join is pushed to SQL
Active relationship -> JOIN in the SQL -> 1 minute
Inactive relationship -> no JOIN -> 3 secondsPlease also test it with a quick change first. Turn on Assume referential integrity on a copy of the file, and compare the Performance Analyzer duration for those same three visuals. If the source has an index on the key columns, the improvement is usually visible straight away.
So I would start with the relationship setting and the indexes, because neither of them needs a change to the DAX or the DirectQuery source. Please let us know the result and mark a reply as the solution if it helps.
AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.