Matrix Performance issue in Direct query

[Anonymous]

Hi Team,

I've used the matrix visual in direct query model, since it fires the query at each level of the hierrachy.

For Example: Matrix visual shown SUM(Sales) by Date and Region
The actual query sent to the source are,
1. select Date,SUM(Sales) from tbl_Sales GROUP BY Date

2 .select Date,Region,SUM(Sales) from tbl_Sales GROUP BY Date,Region

Can someone share the idea for how to improve the matrix performance in direct query mode.

Regards,
Inba

[v-lili6-msft]

hi @Anonymous 

In directquery mode in power bi, there is no data in dataset, so each interaction or adjust in visual filed, it will send a query to datasource, not only in matrix visual, all the visuals have the same logic.

https://radacad.com/directquery-connection-in-power-bi-how-does-it-work-limitations-and-advantages

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-aggregations

Regards,

Lin

[amitchandak]

@Anonymous , make sure you do no have Bi-directional join.

https://insightsquest.com/2020/05/25/simple-directquery-performance-tips/
https://locusit.com/performance-management/power-bi-directquery-performance-improvements/

https://community.powerbi.com/t5/MBAS-Gallery/Aggregation-and-composite-model-Microsoft-Power-BI-fast-and/m-p/712871

[Greg_Deckler]

@Anonymous - Try implementing aggregation tables perhaps. https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-aggregations