cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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 data:

 

 My table is of each contract in our database.  The table contains columns for Corp-Name, Site-name, Site-ID, Contract-Date, Contract-Value, Contract-Expiration value.  It's important to understand that a Site can have multiple contracts, and a customer can have multiple sites.  

 

My goal for the new columns is if a customer has at least one active contract, at any site, then all rows for that customer must equal ACTIVE.  Only once all contracts for all sites have expired are they to be calculated as LOST.  Once a customer is LOST, I want a second column to be stripped with the MAX Expiration date of all the contracts for the sites of that customer.  If active, then remain blank.  Below is a visual of what I would expect the table to look like with the two new columns.  

 

Screen Shot 2020-05-30 at 2.38.54 PM.png

 

With these two new columns, I intend to count them and graph them based on lost date and show the sum of their lost contract values.  I need them in column form for page filtering.

 

Here is how I attempted to calculate the CUSTOMER STATE, however, it is keeping context to the individual rows, and splitting the output for customers who are not truly lost but creating a row for the lost contracts and the active contracts.

Screen Shot 2020-05-30 at 2.57.14 PM.png

 

 

 


Instead of below, this example should be "NOT LOST" instead of both.  

Screen Shot 2020-05-30 at 2.57.32 PM.png

 

 

 

 

 

 

Thank you in advance for your support in helping me get this customer status and lost date resolved!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Responsive Resident
Responsive Resident

Re: Calculated Column Assistance "Customer Contract State"

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.

View solution in original post

Highlighted
Super User IV
Super User IV

Re: Calculated Column Assistance "Customer Contract State"

@Smoody07 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!






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

4 REPLIES 4
Highlighted
Responsive Resident
Responsive Resident

Re: Calculated Column Assistance "Customer Contract State"

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.

View solution in original post

Highlighted
Super User IV
Super User IV

Re: Calculated Column Assistance "Customer Contract State"

@Smoody07 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!






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Highlighted
Responsive Resident
Responsive Resident

Re: Calculated Column Assistance "Customer Contract State"

Yes Parry, ALLEXCEPT is better than my suggestion

Highlighted
Helper I
Helper I

Re: Calculated Column Assistance "Customer Contract State"

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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors