SUMMARIZE and ALLEXCEPT throwing error

[harrame]

Hi all,

I'm building up a rather complex measure but I'm getting a strange error thrown at me. Here's the beginning stage of my measure: 

_Measure Draft = maxx(SUMMARIZE(ALLEXCEPT('Table', 'Table'[SERVICE_WEEK]), [SERVICE_WEEK], "Max Date", max('Table'[SERVICE_DATE]), "Count Days", DISTINCTCOUNT('Table'[SERVICE_DATE])), [SERVICE_WEEK])

The error I get is "The column 'SERVICE_WEEK' specified in the 'SUMMARIZE' function was not found in the input table." When I replace the ALLEXCEPT with ALL and remove Service Week as the filter exception, the error goes away so I'm guessing the issue has to do with the interaction between the ALLEXCEPT and the SUMMARIZE functions. Does anyone have any ideas of what could be happening?

[Anonymous · Accepted solution]

https://dax.guide/allexcept/ - If you read this, you'll know immediately why your code returns the error.

[amitchandak]

@harrame , If you need to group data by week level you don't need all except, because summarize id going to group it

Measure Draft = maxx(SUMMARIZE('Table', 'Table'[SERVICE_WEEK], "Max Date", max('Table'[SERVICE_DATE]), "Count Days", DISTINCTCOUNT('Table'[SERVICE_DATE])), [SERVICE_WEEK])

what is the benefit SUMMARIZE of MAX on service week? That is not using any inner measure 

like, Sum the distinct day by week

Measure Draft = Sumx(SUMMARIZE('Table', 'Table'[SERVICE_WEEK], "Max Date", max('Table'[SERVICE_DATE]), "Count Days", DISTINCTCOUNT('Table'[SERVICE_DATE])), [Count Days])

[DataInsights]

@harrame, the ALLEXCEPT function removes the column SERVICE_WEEK, which makes it unavailable to SUMMARIZE. Try rewriting the SUMMARIZE portion like this:

ADDCOLUMNS (
   SUMMARIZE ( 'Table', 'Table'[SERVICE_WEEK] ),
   "Max Date", MAX ( 'Table'[SERVICE_DATE] ),
   "Count Days", DISTINCTCOUNT ( 'Table'[SERVICE_DATE] )
)