topic Re: SUMMARIZE and ALLEXCEPT throwing error in DAX Commands and Tips

SUMMARIZE and ALLEXCEPT throwing error

Anonymous — Tue, 15 Sep 2020 19:04:50 GMT

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?

Re: SUMMARIZE and ALLEXCEPT throwing error

DataInsights — Wed, 16 Sep 2020 00:06:39 GMT

Anonymous, 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] ) )

Re: SUMMARIZE and ALLEXCEPT throwing error

amitchandak — Wed, 16 Sep 2020 01:24:55 GMT

Anonymous , 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])

Re: SUMMARIZE and ALLEXCEPT throwing error

Anonymous — Wed, 16 Sep 2020 12:50:06 GMT

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