Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

DAX and Power BI Optimization

I m new to DAX and Power BI and need help in improving performace in Power BI. There are 25-30 tables that contain about 15 columns each and is expected to have about 2 million rows. We are using D...
Interesting Links
Need Help
Show and Tell
Tips and Tricks
  • AlbertoFerrari's avatar
    AlbertoFerrari
    8 years ago

    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

v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
8 years ago

Anonymous

 

1. Hiding unused columns

 

Hidden columns are also loaded into Power BI model. Hiding just simplifies the view, it will not improve the performance. 

 

2. Hierarchies and columns

 

Also hiding columns will not impact if the columns are involved in a hierarchy. 

 

3. Removing default summarization from the numeric columns

 

Numeric columns will always be defined an aggregation. It's just a property on column. "Do not aggregate" is also a type. I suggest you not setting "Do not aggregate" on numeric column. 

 

4. DAX expressions on a smaller table vs large table

 

It's better to create measures in table where the columns are present so that Vertipaq Engine doesn't need to iterator the mapping values in anthter table.

 

5. Multiple smaller DAX expressions vs single DAX expression

 

There's no much difference. For better usage, I prefer multiple smaller DAX formula if there will be frequently referenced. 

 

6. Computed Column vs Table in Column

 

Try to avoid calculated column. They are evaluated when the model is updated. It will iterator entire table every time. For more details, refer to links below:

Avoiding calculated column in DAX

Calculated Columns

 

Regards,

 

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

The solution provided is mostly true for Import mode.

However, as AlbertoFerrari mentioned, in Direct query mode the optimizations didn't help me much.

 

I noticed that hiding unused columns, help reduce memory footprint while using PBI Desktop and can significantly impact when you are working in import mode.

 

In Direct Query mode, I noticed that embedding simpler re-usable measures inside the queries (Ex: MAX('Column'[a]) can be made a separate measure that can be used in sevral DAX expressions) brought down the total number of queries that were sent for processing and the waiting time involved. Ths actually resulted in slightly faster execution of queries.

 

Thanks,

Mannu