How to use ALLEXCEPT

[mirf]

Hi, I get this message when using dax in Power BI. If I use ALL, then the formula is working correctly. The formula is not working when I use ALLEXCEPT. Could you please help? Thank you

Message

A single value for column 'MONTH' in table 'TS_OUTLOOK' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

DAX:

VALUEA = CALCULATE(SUM(TS_OUTLOOK[Value]),FILTER(ALLEXCEPT('TS_OUTLOOK',TS_OUTLOOK[YEAR],TS_OUTLOOK[MONTH]),TS_OUTLOOK[MONTH]=6 && TS_OUTLOOK[YEAR]=2024))

[tharunkumarRTK · Accepted solution]

@mirf 

There is an issue in your measure. 

ALLEXCEPT is a table function and its syntax should be

ALLEXCEPT ( <TableName>, <ColumnName> [, <ColumnName> [, … ] ] )

 the resultant table will contain all columns except the list of columns that you added. For example in your case,

VALUEA = CALCULATE(SUM(TS_OUTLOOK[Value]),FILTER(ALLEXCEPT('TS_OUTLOOK',TS_OUTLOOK[YEAR],TS_OUTLOOK[MONTH]),TS_OUTLOOK[MONTH]=6 && TS_OUTLOOK[YEAR]=2024))

the result of ALLEXCEPT, contains all columns in TS_OUTLOOK except MONTH and YEAR columns, since the resultant does not contain those columns but you added additoinal filters to them (TS_OUTLOOK[MONTH]=6 && TS_OUTLOOK[YEAR]=2024) because of which you got the error.

Please checkout this article to know more about ALLEXCEPT: https://dax.guide/allexcept/

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!