Dax Measure Help

drwinny · ‎07-20-2021

Hi All,

I created this measure to tell me how many technical escalations have been open (confirmed) for more than 30 days but less than 60 days

[StatusCodeName] = "confirmed" means the item is open

[DaysOpen] = is the number of days the item has been open for

When I used this code in a card visual it shows a total (that looks correct) but when it's added to a table, I get this error.

MdxScript(Model) (173, 3) Calculation error in measure 'vwTechnicalEscalations'[*TE OPEN more than 30 less than 60 days]: SummarizeColumns() and AddMissingItems() may not be used in this context.

*TE OPEN more than 30 less than 60 days = 

  VAR __DS0FilterTable = 
    TREATAS({"Confirmed"}, 'vwTechnicalEscalations'[statuscodename])

  VAR __DS0FilterTable2 = 
    FILTER(
      KEEPFILTERS(VALUES('vwTechnicalEscalations'[DaysOpen])),
      AND(
        'vwTechnicalEscalations'[DaysOpen] >= 31,
        'vwTechnicalEscalations'[DaysOpen] <= 60
      )
    )

  VAR __DS0FilterTable3 = 
    FILTER(
      KEEPFILTERS(VALUES('DimDate'[FullDateAlternateKey])),
      'DimDate'[FullDateAlternateKey] >= DATE(2019, 1, 1)
    )

Return
  SUMMARIZECOLUMNS(
    __DS0FilterTable,
    __DS0FilterTable2,
    __DS0FilterTable3,
    "v_Total_Escalations", IGNORE('vwTechnicalEscalations'[*Total Escalations])
  )

Can someone help me change the measure so it can be used in a table, please?

Thanks,

Sean