Forum Discussion

Clint's avatar
Clint
Helper V
7 years ago
Solved

Understanding results returned by CountAX function

Hello,

 

I have a table that has records with various status values.  Ideally, I'd like to count all records except for those in certain statuses but I have not been able to figure out the dax to make that work.  As an alternative, I built a measure to count only those records in the statuses I want to remove from the total so I can subtract this value from the overall count.  
I am using the following to count the records w/the statuses I will later subtract:

M_Inactive Items = COUNTAX('Boydenhancements','Boydenhancements'[Status] iN {"Removed","N/A","Completed","Not Approved"}
)
The problem is, this measure should return a number of 48 but instead, it returns the total count of all records which is 232.  Not sure why it is ignoring the IN part of the expression but would appreciate some insight as to where I"m going wrong here...
Need Help
  • MFelix's avatar
    MFelix
    7 years ago
    Hi Clint try the following measure,

    COUNTAX (
    FILTER (
    'Boydenhancements',
    'Boydenhancements'[Status] IN { "Removed", "N/A", "Completed", "Not Approved" }
    ),
    'Boydenhancements'[Status]
    )

    Regards,
    MFelix

4 Replies

    • Clint's avatar
      Clint
      Helper V
      7 years ago

      Hi Vanessa - MFelix was able to show me the correct syntax.  Thank you for the response.

  • MFelix's avatar
    MFelix
    Super User
    7 years ago
    Hi Clint try the following measure,

    COUNTAX (
    FILTER (
    'Boydenhancements',
    'Boydenhancements'[Status] IN { "Removed", "N/A", "Completed", "Not Approved" }
    ),
    'Boydenhancements'[Status]
    )

    Regards,
    MFelix