Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Improve Performance

Hi all,

 

I`ve been using PBI for around 3 years but learned mostly online, so still learning about best practices. I`m having a small issue because I`m pulling raw data from a server that I have read only access. I`m doing all the cleanup in PowerQuery (removing columns, changing data type). I have some lookups done in Dax as well. Someone came asking if I could improve the performance because the server it`s a bit heavy. If I can`t get some temp tables to cleanup before PBI presents what would be my options? I already did those steps in Power Query:

  • Removed lots of unused columns
  • Removed time from Date
  • Changed some columns data type

I was asked to remove my DAX columns and do everything in the PowerQuery but lookups can`t be done without merging (and that takes lots of processing). So I`m a bit lost. 

 

Thanks for your help

 

Need Help

4 Replies

  • Idrissshatila's avatar
    Idrissshatila
    Super User
    2 years ago

    Hello Anonymous ,

     

    well, its better to do the work in power query rather than doing it as calculated columns or calculated tables.

     

    try grouping by if you could, this would make the data smaller.

     

    try implementing incremental refresh thus it won't refresh all data every time, just a range of date that you choose.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      I tried doing as well, some of those are fine. For Lookup specifically have to merge tables and I was told that this is taking a lot from the servers as well. That`s why I`m looking for other options in case this not being enough. It seems that file size it`s not the issue but how much processing the file is taking.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    If your data source is something like SQL, you can try contacting someone with editing rights to the data source to perform filtering and transformations at the source using a native database query language such as SQL. This will greatly reduce the load pressure on Power BI.
    With regard to DAX, I recommend that you download DAX Studio to monitor the timestamps used by DAX statements as a way to find the DAX statements that are causing poor performance and try to modify them.

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

  • Brunner_BI's avatar
    Brunner_BI
    Impactful Individual
    2 years ago

    You should run Measure Killer to see which measures and columns are unused anywhere and then remove them.