Forum Discussion
Count (Customerid) except certain confition
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 |
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
2 Replies
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
Dear Owen ,
I appreciate your answer. I got the way your appraoch, very good. I'll try to implement.
Regards
