Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Weak relationship issue

Hi All, I am creating a table visual in the report. The whole model is using DirectQuery.  However, one of the dataset is quite large (>15M), and it takes so long to refresh the table visual(~15sec...
Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi Anonymous ,

     

    You can not avoid the limited relationship. If the relationship between two different mode tables, it will be limited relationship.

     

    Table expansion never occurs for limited relationships. Table joins are achieved by using semantics, and for this reason, blank virtual rows aren't added to compensate for referential integrity violations.

    This is the reason why some data will be missing in the table visual.

     

    Mostly, many reasons lead to a long time for refreshing. It may be caused by a delay in the database, or by too much complexity in the DAX formula. For example, SQL Server Profiler, DAX Studio or Performance analyzer.

     

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

amitchandak, for the report requirement, it is needed to use direct query to get the real-time data.

However, seems that when using limited relationship, if there are data in Fact table(Direct query) but not in DimTable(Import), those rows cannot be shown in the table visual.

https://docs.microsoft.com/en-us/power-bi/guidance/relationships-troubleshoot

 

I am seeking a way to enhance the performace for that large DimTable whereas with the correct data.

 

For now, what I can find is Hybrid model but it required premium per user

v-chenwuz-msft's avatar
v-chenwuz-msft
Community Support
4 years ago

Hi Anonymous ,

 

You can not avoid the limited relationship. If the relationship between two different mode tables, it will be limited relationship.

 

Table expansion never occurs for limited relationships. Table joins are achieved by using semantics, and for this reason, blank virtual rows aren't added to compensate for referential integrity violations.

This is the reason why some data will be missing in the table visual.

 

Mostly, many reasons lead to a long time for refreshing. It may be caused by a delay in the database, or by too much complexity in the DAX formula. For example, SQL Server Profiler, DAX Studio or Performance analyzer.

 

Best Regards

Community Support Team _ chenwu zhu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.