Forum Discussion

CU08101843's avatar
CU08101843
New Member
1 day ago

Visual initial loading time retrieval

hi desperate need help

I have a report that connects to a database in DirectQuery mode. However, the file is large (9 MB). When users first launch the report, the three visuals show as empty with continuous loading icons and take several minutes to appear. Is there a solution to reduce the initial loading time to 1–2 seconds? I cannot modify the DAX measure and Direct query because I will get blamed. How can I achieve an initial loading time of under 2 seconds if the relationship remains active?

this is how it works

When the relationship is active, the initial loading of these 3 visuals in Power BI takes 1 minute.

When the relationship is inactive, the initial loading takes 3 seconds.

How can I achieve the initial loading of under 2 seconds if the relationship is active?

Need Help

2 Replies

  • jubinsoni's avatar
    jubinsoni
    Icon for Advocate IV rankAdvocate IV
    9 hours ago

    Hi 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 seconds

    Please 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.

  • Praful_Potphode's avatar
    Praful_Potphode
    Icon for Super User rankSuper User
    1 day ago

    Hi CU08101843​ 

    we cannot bring the loading time down all of sudden.below are some of the best practices that you can follow in your model.

    1. try to change storage mode of your powerapp table to import.
    2. use performance analyzer to identify which visual is loading slow.if its table with 23 column then reduce the columns in landing page so that it loads fast.create a drillthrough page which has all 23 columns.
    3. try to save your report with default filters applied so that data load is less which in turn improves visual load time.

    Please give kudos or mark it as solution once confirmed.

    Regards,

    Praful