HELP UNDERSTANING ALL AND ALLEXCEPT

[Anonymous]

Hello,

I just happened to try this out to get the number of parts,was more of a trial but is there a place where i can read
how the below functions were applied.I read that Count would bring the count of the values in the column but how is
ALLEXCEPT AND ALL altering the result.

Count_part = calculate(COUNT('Spend'[Part]),ALLEXCEPT('Spend','Spend '[Part] ))

Colour Part count_part
Black      A     3
Red        A     3
Blue        A    3
Blue         B    1

Count_part = calculate(COUNT('Spend'[Part]),ALL('Spend','Spend '[Part] ))

Colour Part count_part
Black      A    1
Red         A   1
Blue         A   1
Blue         B    1

Thanks

[v-cazheng-msft]

Hi @Anonymous 

According to the syntax of CALCULATE function, Calculate(expression, filter). Calculate will use the parameter ‘filter’ to modify the filter context, choose or remove rows and then calculate the ‘expression’.

All and AllEXCEPT can be used as filters of CALCULATE. All(table/columns) will remove all filters on the table or columns and return all the rows. So the result will not be affect by the type of ‘Colour’ and ‘Part’.  ALLEXCEPT (table, columns) function will remove all filters on the table except the filters on the parameter ‘columns”. So the result will be affected by the type of ‘Part’ but not ‘Colour’

For more details about using ALL and ALLEXCEPT, you can refer How to use ALL and ALLEXCEPT Function in Power BI DAX.

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[negi007 · Accepted solution]

@Anonymous 

please refer to below links for help

https://dax.guide/allexcept/

https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

https://www.sqlbi.com/blog/marco/2010/04/05/all-allexcept-and-values-in-dax/

[Anonymous]

@v-cazheng-msft @negi007 

Thanks for helping