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

Complicated Rule based formula

Hello. This might be complicated. 

I have a claims table that is the lookup table and a work table attached. The work order tables has work order number, work order type, work order pricing agreement and work order status. 

I want to associate work orders with a "type" = "cashout" with their pricing agreement type. 

Here are the rules:

1) For ALL cashouts (cashouts are only counted if work order status= closed/completed), use the pricing agreement of the first work order on that specific claim. 

2) If there is a redispatch work order AND the status of the first time dispatch = closed/cancelled

3) Only count cashouts if they have a status of "closed/completed"

4) Sort all this based on claims created date. 

 

Picture of spreadsheet attached. Thanks!!

 

Helpie.png

2 REPLIES 2
Highlighted
Helper III
Helper III

Re: Complicated Rule based formula

I was able to work this out in the claims table, but this still doesn't help me with the pricing agreement aspect of things. 

 

=CALCULATE(counta(WorkOrder[WO Type]),WorkOrder[WO Type]="Cashout",WorkOrder[Status]="Closed/Completed")

Highlighted
Community Support
Community Support

Re: Complicated Rule based formula

Hi @ammartino44,

 

You can try to use bleow measure if it works on your side.

 

Count of Specify Rules=
var currType= LastNoBlank(TABLE[Pricing Agreement],[Pricing Agreement])
return
COUNTROWS(
FILTER(
ALL(TABLE),
[Pricing Agreement]=currType&&
OR(
OR([WO Type]="First TIme Dispath",[WO Type]="Cashout")&&[Status]= "closed/completed", 
[WO Type]="Redispath"&&[Status]= "closed/cancelled")))

 

If above is not help, please share me a sample file to test.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

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.

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