topic Using both ALL and ALLEXCEPT together? in DAX Commands and Tips

Using both ALL and ALLEXCEPT together?

BugmanJ — Thu, 23 Feb 2023 13:19:58 GMT

Hello All,

Simplistic table:

YEAR

COMPANY

OrderNumber

[YEAR] is linked to a Calander Table which also has [YEAR]

I have two slicers on the page, YEAR and COMPANY

I am trying to create a measure to count the number of OrderNumbers ignoring the COMPANY but keeping the YEAR

NumberofOrders = CALCULATE ( DISTINCTCOUNT('Table'[OrderNumber]), ALL('Table'), ALLEXCEPT('Calendar','Calendar'[YEAR]) )

However this doesnt seem to work. Any ideas on how to correct?
Thank you

Re: Using both ALL and ALLEXCEPT together?

ValtteriN — Thu, 23 Feb 2023 11:02:22 GMT

Hi,
Since you want to remove one filter and keep the other try using REMOVEFILTER:

DAX:

Measure 39 = CALCULATE(DISTINCTCOUNT('Table (25)'[OrderNumber]),REMOVEFILTERS('Table (25)'[Company]))

End result:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: Using both ALL and ALLEXCEPT together?

BugmanJ — Thu, 23 Feb 2023 13:21:05 GMT

tamerj1 Please see PM

tamerj1 Original message edited to give more details

Re: Using both ALL and ALLEXCEPT together?

tamerj1 — Thu, 23 Feb 2023 13:24:39 GMT

BugmanJ
Please try

NumberofOrders = CALCULATE ( DISTINCTCOUNT ( 'Table'[OrderNumber] ), ALLEXCEPT ( 'Table', 'Calendar'[Date] ) )

Re: Using both ALL and ALLEXCEPT together?

BugmanJ — Thu, 23 Feb 2023 14:31:46 GMT

tamerj1 / ValtteriN

Slight variance on the above. In the Data above, lets say I have an extra coloumn called [TIMETOPRODUCE] but I want that to filter out orders less then 155

NumberofOrders = CALCULATE ( DISTINCTCOUNT ( 'Table'[OrderNumber] ), ALLEXCEPT ( 'Table', 'Calendar'[Date] ), FILTER ( 'Table'[TimeToProduce]<155) )

Doesnt work, any ideas?
J

Re: Using both ALL and ALLEXCEPT together?

tamerj1 — Thu, 23 Feb 2023 15:06:21 GMT

BugmanJ

NumberofOrders = CALCULATE ( DISTINCTCOUNT ( 'Table'[OrderNumber] ), ALLEXCEPT ( 'Table', 'Calendar'[Date] ), 'Table'[TimeToProduce] < 155 )

Re: Using both ALL and ALLEXCEPT together?

BugmanJ — Thu, 23 Feb 2023 15:32:18 GMT

Thank you tamerj1