Forum Discussion
How to use ALLEXCEPT
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))
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!
1 Reply
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!