Forum Discussion
DAX and Power BI Optimization
Unfortunately, you are looking in the wrong direction.
Most of your questions are about DAX and the Power BI model but, if you use DirectQuery, there is not much that you can do in DAX to improve performance. We analyzed the performance and implications of DirectQuery in this whitepaper: https://www.sqlbi.com/articles/directquery-in-analysis-services-2016/.
The quick conclusion is: try to reduce DAX usage to a minimum and super-optimize your SQL database so that it will answer quickly to the specific queries Power BI will generate. The description of the reasons is in the whitepaper.
Besides, the real question here is: why using DirectQuery at all? Your model is a tiny one, the data refresh should be very fast and, by avoiding DirectQuery, you would remove most of your speed issues.
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.com
Unfortunately, you are looking in the wrong direction.
Most of your questions are about DAX and the Power BI model but, if you use DirectQuery, there is not much that you can do in DAX to improve performance. We analyzed the performance and implications of DirectQuery in this whitepaper: https://www.sqlbi.com/articles/directquery-in-analysis-services-2016/.
The quick conclusion is: try to reduce DAX usage to a minimum and super-optimize your SQL database so that it will answer quickly to the specific queries Power BI will generate. The description of the reasons is in the whitepaper.
Besides, the real question here is: why using DirectQuery at all? Your model is a tiny one, the data refresh should be very fast and, by avoiding DirectQuery, you would remove most of your speed issues.
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.com
Thanks for the response Alberto.
At this point data size might be small. But we are expecting 10 million rows of data in each table.
Hence Direct query seems to be the only option.
Dashboard is complex with about 18 data slicers and 10 visuals.
Found 30-40 queries being fired simulataneously.
Minimum time to load seems to be 1-2 minutes.
Hence wanted to check if DAX optimizations can bring down total number of queries, reduce complexity of queries ..etc