Forum Discussion

rmeng's avatar
rmeng
Icon for Helper II rankHelper II
5 years ago

Dax Query too long when disabling Edit Interactions Filter

I Have a Matrix and works fine, but when I disable the Edit Interactions filter to show all months the query takes 50 seconds.

I have this code from DAX Studio can you tell me what´s wrong?

 

// DAX Query
DEFINE
  VAR __DS0FilterTable = 
    FILTER(
      KEEPFILTERS(VALUES('DIM CALENDAR'[MONTH_YEAR])),
      NOT('DIM CALENDAR'[MONTH_YEAR] IN {BLANK()})
    )

  VAR __DS0FilterTable2 = 
    FILTER(
      KEEPFILTERS(VALUES('DIM HCO'[TERRITORY_NUMBER])),
      AND(
        NOT(SEARCH("EISAI", 'DIM HCO'[TERRITORY_NUMBER], 1, 0) >= 1),
        NOT(ISBLANK('DIM HCO'[TERRITORY_NUMBER]))
      )
    )

  VAR __DS0FilterTable3 = 
    FILTER(
      KEEPFILTERS(VALUES('DIM MSL'[MSL])),
      NOT('DIM MSL'[MSL] IN {BLANK()})
    )

  VAR __DS0FilterTable4 = 
    FILTER(
      KEEPFILTERS(VALUES('DIM GEO'[REGION_NAME])),
      NOT('DIM GEO'[REGION_NAME] IN {BLANK()})
    )

  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL('DIM HCO'[ACCOUNT_NAME], "IsGrandTotalRowTotal"),
      ROLLUPADDISSUBTOTAL(
        ROLLUPGROUP('DIM CALENDAR'[MONTH_YEAR], 'DIM CALENDAR'[ORDER_MONTH_YEAR]), "IsGrandTotalColumnTotal"
      ),
      __DS0FilterTable,
      __DS0FilterTable2,
      __DS0FilterTable3,
      __DS0FilterTable4,
      "v_Interactions", 'INTERACTIONS'[#Interactions],
      "v__Focused", 'INTERACTIONS'[% Focused],
      "v__Section_Score", 'SITE READINESS SECTION'[# Section Score],
      "v__Site_Overall_Score", IGNORE('SITE READINESS OVERALL'[# Site Overall Score])
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(
      102,
      SUMMARIZE(__DS0Core, 'DIM HCO'[ACCOUNT_NAME], [IsGrandTotalRowTotal]),
      [IsGrandTotalRowTotal],
      0,
      'DIM HCO'[ACCOUNT_NAME],
      1
    )

  VAR __DS0SecondaryBase = 
    SUMMARIZE(
      __DS0Core,
      'DIM CALENDAR'[MONTH_YEAR],
      'DIM CALENDAR'[ORDER_MONTH_YEAR],
      [IsGrandTotalColumnTotal]
    )

  VAR __DS0Secondary = 
    TOPN(
      102,
      __DS0SecondaryBase,
      [IsGrandTotalColumnTotal],
      1,
      'DIM CALENDAR'[ORDER_MONTH_YEAR],
      1,
      'DIM CALENDAR'[MONTH_YEAR],
      1
    )

  VAR __DS0BodyLimited = 
    NATURALLEFTOUTERJOIN(
      __DS0PrimaryWindowed,
      SUBSTITUTEWITHINDEX(
        __DS0Core,
        "ColumnIndex",
        __DS0Secondary,
        [IsGrandTotalColumnTotal],
        ASC,
        'DIM CALENDAR'[ORDER_MONTH_YEAR],
        ASC,
        'DIM CALENDAR'[MONTH_YEAR],
        ASC
      )
    )

EVALUATE
  __DS0Secondary

ORDER BY
  [IsGrandTotalColumnTotal],
  'DIM CALENDAR'[ORDER_MONTH_YEAR],
  'DIM CALENDAR'[MONTH_YEAR]

EVALUATE
  __DS0BodyLimited

ORDER BY
  [IsGrandTotalRowTotal] DESC, 'DIM HCO'[ACCOUNT_NAME], [ColumnIndex]

No RepliesBe the first to reply