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!

speedramps's avatar
speedramps
Icon for Super User rankSuper User
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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks a ton!  You hit the nail on the head for the issue.  I completely forgot to get the except statement in there.