cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

Optimización de DAX

Hola, chicos

Uno de mis informes está funcionando bastante lento en comparación con otros informes. He estado usando el analizador de rendimiento para tratar de determinar qué está causando que el DAX se ejecute bastante lento. Por mi vida no puedo imaginar cómo puedo mejorarla, consulte las consultas de DAX a continuación:

Presupuesto (10 segundos):

// 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 segundos):

// 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]

2 REPLIES 2
Syndicate_Admin
Administrator
Administrator

Hola @ajbogle ,
Puede probar los siguientes métodos:
1. Puede usar la herramienta externa DaxStudio para encontrar la ejecución máxima y analizarla.
https://daxstudio.org/

2. Si está utilizando el modo importar conexión, puede cambiarlo al modo de conexión de consulta directa para acelerar la frecuencia de actualización.
https://community.powerbi.com/t5/Desktop/DAX-and-Power-BI-Optimization/m-p/268589

3. Optimice el modelo en power bi para reducir columnas y datos innecesarios.
https://docs.microsoft.com/en-us/power-bi/guidance/power-bi-optimization

Resuelto: Transformaciones en Power Query - Rendimiento muy lento... - Microsoft Power BI Community

4. Optimización de Dax:
https://maqsoftware.com/insights/dax-best-practices

5. Optimización del modelado:
https://community.powerbi.com/t5/Desktop/DAX-optimization/td-p/946984


Saludos
Liu Yang
Si esta publicación ayuda, considere Aceptarla como la solución para ayudar a los otros miembros a encontrarla más rápidamente.

Syndicate_Admin
Administrator
Administrator

¿Qué versión de Power BI Desktop está ejecutando?

¿Es un modelo directquery o import o mixto? Intentar determinar si alguno de sus DAX se traduce a SQL nativo. En caso afirmativo, entonces el nivel de complejidad de la solución del problema se disparó.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors