# DAX Calculated column ALLEXCEPT v ALL / Values;

1. ## DAX Calculated column ALLEXCEPT v ALL / Values;

Workbook included.

Hi,

I have a measure ;

Total Item:=CALCULATE([Total],ALL(Table1),VALUES(Table1[Item]) )

which give a total for items, I would like to replicate this as a calculated column using ALL and Values.

I have got it to work uisng ALLEXCEPT ; ``Please Login or Register  to view this content.``
but I cannot get ALL / VALUES to work ; I have tried ; ``Please Login or Register  to view this content.``
which I thought would work the same, but I just get an overall total and I've also tried; ; ``Please Login or Register  to view this content.``
and I'm not quite sure how this in calculating?

How can I use ALL and VALUES to get the result I want?

Richard.  Register To Reply

2. ## Re: DAX Calculated column ALLEXCEPT v ALL / Values;

I can't imagine why you would, but it would need to be:

=CALCULATE([Total],ALL(Table1[Date]),ALL(Table1[Units]),VALUES(Table1[Item]) )

in order to override the row context filter on the Units column - which is why you get the answers you do with your CALCULATE([Total],ALL(Table1[Date]),VALUES(Table1[Item]) ) version.  Register To Reply

3. ## Re: DAX Calculated column ALLEXCEPT v ALL / Values;

Thanks, what about where I used ALL Table ; ``Please Login or Register  to view this content.``
Isn't that removing the filter context form the Date and Units ? but seem to ignore the VALUES, I can't find much/ anything on
the difference, if any, between ALL / VALUES in a Calculated column and measure.

Richard.  Register To Reply

4. ## Re: DAX Calculated column ALLEXCEPT v ALL / Values;

VALUES is an iterator and in the row context of a calculated column, it returns all the Item values for each row of the table, so it actually has no effect when combined with your ALL(Table1).  Register To Reply

5. ## Re: DAX Calculated column ALLEXCEPT v ALL / Values;

Thank you,

RD  Register To Reply