Optimizing DAX

[ajbogle]

Hi guys,

One of my reports is running rather slow compared to other reports. I've been using the performance analyzer to try and determine what is causing the DAX to run rather slow. For the life of me I can't figure how I can improve it, please see DAX queries below:

Budget (10 seconds):

// DAX Query
DEFINE
  VAR __DS0FilterTable = 
    FILTER(
      KEEPFILTERS(VALUES('Operations'[Current HIA])),
      AND(
        'Operations'[Current HIA] >= DATE(2022, 5, 8),
        'Operations'[Current HIA] < DATE(2022, 8, 6)
      )
    )

  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL('Operations'[Market], "IsGrandTotalRowTotal"),
      __DS0FilterTable,
      "Delta__Annualized_Total_Field_and_Market__", 'Gusto'[Delta: Annualized Total Field and Market $],
      "Budget__Total_Field_and_Market__", 'Gusto'[Budget: Total Field and Market $],
      "Actuals__Total_Field_and_Market__", 'Gusto'[Actuals: Total Field and Market $],
      "Delta__Total_Field_and_Market__", 'Gusto'[Delta: Total Field and Market $],
      "Projected_Installs", 'Operations'[Projected Installs],
      "Budget__Total_Field_and_Market", 'Gusto'[Budget: Total Field and Market],
      "Actual__Total_Field_and_Market", 'Gusto'[Actual: Total Field and Market],
      "Delta__Total_Field_and_Market", 'Gusto'[Delta: Total Field and Market],
      "Current_Level__Total", 'Operations'[Current Level: Total],
      "Budget__Annualized_Total_Field_and_Market__", 'Gusto'[Budget: Annualized Total Field and Market $],
      "Actuals__Annualized_Total_Field_and_Market__", 'Gusto'[Actuals: Annualized Total Field and Market $],
      "Projected_Installs_Annually", 'Operations'[Projected Installs Annually]
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(
      501,
      __DS0Core,
      [IsGrandTotalRowTotal],
      1,
      [Delta__Annualized_Total_Field_and_Market__],
      0,
      'Operations'[Market],
      1
    )

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  [IsGrandTotalRowTotal],
  [Delta__Annualized_Total_Field_and_Market__] DESC,
  'Operations'[Market]

Delta (4.3 seconds):

// DAX Query
DEFINE
  VAR __DS0FilterTable = 
    FILTER(
      KEEPFILTERS(VALUES('Operations'[Current HIA])),
      AND(
        'Operations'[Current HIA] >= DATE(2022, 5, 8),
        'Operations'[Current HIA] < DATE(2022, 8, 6)
      )
    )

  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL('Operations'[Market], "IsGrandTotalRowTotal"),
      __DS0FilterTable,
      "Delta__Crew_Count", 'Operations'[Delta: Crew Count],
      "Delta__Ops_Mgr___", 'Gusto'[Delta: Ops Mgr. $],
      "Delta__Roof_Lead__", 'Gusto'[Delta: Roof Lead $],
      "Delta__Additional_Crew__", 'Gusto'[Delta: Additional Crew $],
      "Delta__Electrician__", 'Gusto'[Delta: Electrician $],
      "Delta__Apprentice_Electrician__", 'Gusto'[Delta: Apprentice Electrician $],
      "Delta__Install_Manager__", 'Gusto'[Delta: Install Manager $],
      "Delta__Warehouse_Supervisor__", 'Gusto'[Delta: Warehouse Supervisor $],
      "Delta__Supply_Chain_Specialist__", 'Gusto'[Delta: Supply Chain Specialist $],
      "Delta__Service_Supervisor__", 'Gusto'[Delta: Service Supervisor $],
      "Delta__Service_Technician__", 'Gusto'[Delta: Service Technician $],
      "Delta__Lead_Surveyor__", 'Gusto'[Delta: Lead Surveyor $],
      "Delta__Site_Surveyor__", 'Gusto'[Delta: Site Surveyor $],
      "Delta__Field_Office_Admin__", 'Gusto'[Delta: Field Office Admin $],
      "Delta__Branch_Coordinator__", 'Gusto'[Delta: Branch Coordinator $],
      "Delta__Total_Field__", 'Gusto'[Delta: Total Field $]
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'Operations'[Market], 1)

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  [IsGrandTotalRowTotal] DESC, 'Operations'[Market]

[Duia]

Hi @ajbogle ,
You can try the following methods:
1. You can use the external tool DaxStudio to find the maximum run and analyze it.
https://daxstudio.org/

2. Are you using the Import connection mode, you can change it to the Direct query connection mode to speed up the refresh rate.
https://community.powerbi.com/t5/Desktop/DAX-and-Power-BI-Optimization/m-p/268589

3. Optimize the model in power bi to reduce unnecessary columns and data.
https://docs.microsoft.com/en-us/power-bi/guidance/power-bi-optimization

Solved: Transformations in Power Query - Very Slow Perform... - Microsoft Power BI Community

4. Dax optimization:
https://maqsoftware.com/insights/dax-best-practices

5. Modeling optimization:
https://community.powerbi.com/t5/Desktop/DAX-optimization/td-p/946984

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

[Element115]

What version of Power BI Desktop are you running?

Is it a DirectQuery or Import model or mixed?  Trying to determine if any of your DAX gets translated to native SQL. If yes, then the complexity level of troubleshooting the issue just shot up.

[ajbogle]

The entire model is Import, I'm connecting directly to QuickBase through their connector in PowerBI