Forum Discussion

BugmanJ's avatar
BugmanJ
Helper V
3 years ago
Solved

Using both ALL and ALLEXCEPT together?

Hello All,

Simplistic table:

YEARCOMPANYOrderNumber

 

[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

  • tamerj1's avatar
    tamerj1
    3 years ago

    BugmanJ 
    Please try

     

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

     

6 Replies

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    3 years ago

    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/

    • tamerj1's avatar
      tamerj1
      Community Champion
      3 years ago

      BugmanJ 
      Please try

       

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

       

  • BugmanJ's avatar
    BugmanJ
    Helper V
    3 years ago

    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


    • tamerj1's avatar
      tamerj1
      Community Champion
      3 years ago

      BugmanJ 

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