Count (Customerid) except certain confition

[uaydogdu]

Dear All,

I want to get the number of active (AC) customers which are not discontinue (DC) in the same time period.

Here is an example:

I would like to count of Id's in Year 2010.  While counting, I need to exclude those IDs whose status  'AC' and 'DC' in 2010. 

I would like to report yearly.

2010 ... 3

2011 ... 4

Is it possible with DAX?

Thanks in Advance

ID	Status	Date
26854	AC	05/01/2010
26854	DC	05/02/2010
26855	AC	06/02/2010
26856	AC	07/02/2010
26856	AC	08/02/2010
26854	AC	09/02/2011
26855	AC	11/02/2011
26856	AC	12/02/2011
26857	AC	13/02/2011

[OwenAuger · Accepted solution]

Hi @uaydogdu 

I would suggest something along these lines:

Customer Count AC but not DC =
VAR AC_Customers =
    CALCULATETABLE ( DISTINCT ( YourTable[ID] ), YourTable[Status] = "AC" )
VAR DC_Customers =
    CALCULATETABLE ( DISTINCT ( YourTable[ID] ), YourTable[Status] = "DC" )
RETURN
    COUNTROWS ( EXCEPT ( AC_Customers, DC_Customers ) )

This measure just takes the set difference between Customers with Status AC and Customers with Status DC, and counts the resulting Customers.

This should give the result you expect when filtered by Year (or any other time period).

Does this work for you?

Regards,

Owen

[uaydogdu]

Dear Owen ,

I appreciate your answer. I got the way your appraoch, very good. I'll try to implement. 

Regards