Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculated Column Assistance "Customer Contract State"

Good afternoon Team,   I am looking for assistance in writing the DAX necessary to create a calculated column to help determine if a customer is lost or active and when.  Here is the context of my ...
Need Help
  • speedramps's avatar
    speedramps
    6 years ago

    H Smoody

    Pleaase consider ths solution and leave kudos

     

    Here are some DAX measure you can tweak ....

     

    AllContractsForCustomer =
    /*
    This measure counts all contracts for a customer.
    The ALL removes all the filters including the row context filter, then VALUES reapplies the customer context
    */
    CALCULATE (
    COUNTROWS(Contracts),
    All (Contracts),
    VALUES(Contracts[Customer])
    )


    ActiveContractsForCustomer =

    /*
    This measure counts all active contracts for the customer.
    The ALL removes all the filters including the row context filter, then VALUES reapplies the customer context.
    Then is just filters ACTIVE
    */
    CALCULATE (
    COUNTROWS(Contracts),
    All (Contracts),
    VALUES(Contracts[Customer]),
    Contracts[Contract status]="ACTIVE"
    )

     

     

    InactiveContractsForCustomer =

    /*
    This measure counts all inactive contracts for the customer.
    The ALL removes all the filters including the row context filter, then VALUES reapplies the customer context.
    Then is just filters INACTIVE
    */
    CALCULATE (
    COUNTROWS(Contracts),
    All (Contracts),
    VALUES(Contracts[Customer]),
    Contracts[Contract status]="INACTIVE"
    )

     

    I assume you just needed help overriding the row context. and you can do the rest from now on, because you seem to have a good grasp of IF logic.

  • parry2k's avatar
    parry2k
    6 years ago

    Anonymous add two calculated columns

     

    Lost Y-N = 
VAR __countActive = 
CALCULATE ( 
COUNTROWS ( Table ), 
ALLEXCEPT ( Table, Table[Customer] ),
Table[Contract State] = "Active"
)
RETURN
IF ( __countActive >= 1, "Active", "Inactive" )

     

    Most Recent Expiration Date = 
IF ( Table[Lost Y-N] = "Inactive", 
CALCULATE ( 
MAX ( Table[Expiration Date]), 
ALLEXCEPT ( Table, Table[Customer] )
)

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

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

Anonymous add two calculated columns

 

Lost Y-N = 
VAR __countActive = 
CALCULATE ( 
COUNTROWS ( Table ), 
ALLEXCEPT ( Table, Table[Customer] ),
Table[Contract State] = "Active"
)
RETURN
IF ( __countActive >= 1, "Active", "Inactive" )

 

Most Recent Expiration Date = 
IF ( Table[Lost Y-N] = "Inactive", 
CALCULATE ( 
MAX ( Table[Expiration Date]), 
ALLEXCEPT ( Table, Table[Customer] )
)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

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

Yes Parry, ALLEXCEPT is better than my suggestion