Forum Discussion

uaydogdu's avatar
uaydogdu
Icon for Helper I rankHelper I
6 years ago
Solved

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,...
  • OwenAuger's avatar
    OwenAuger
    6 years ago

    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

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
6 years ago

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's avatar
    uaydogdu
    Icon for Helper I rankHelper I
    6 years ago

    Dear Owen ,

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

    Regards