Forum Discussion
Flag data at Certain Threshold
Hello - I am looking for some quick help here to meet a tight deadline. I am looking to flag certain customers in my data set once their aggregate $ amount hits a certain threshold (in this case $3,...
parry2k this is what I would expect to see. Contracts are in sequential numeric order as well.
| Date | Customer | Contract | $ Value | Flagged |
| 1/1/2019 | Customer A | Contract 1 | 1000 | |
| 1/1/2019 | Customer B | Contract 2 | 1000 | |
| 2/1/2019 | Customer A | Contract 3 | 2000 | |
| 2/1/2019 | Customer C | Contract 4 | 2000 | |
| 3/1/2019 | Customer A | Contract 5 | 250 | |
| 3/1/2019 | Customer A | Contract 6 | 500 | Y |
| 3/1/2019 | Customer B | Contract 7 | 2000 | |
| 4/1/2019 | Customer A | Contract 8 | 500 | Y |
| 4/1/2019 | Customer C | Contract 9 | 500 | |
| 4/1/2019 | Customer C | Contract 10 | 1500 | Y |
Anonymous you can add index column in power query and then it is easy
Flagged 1 = VAR __value =
CALCULATE( SUM( Table7[$ Value] ),FILTER( ALLEXCEPT(Table7, Table7[Customer] ),
Table7[Date] <= MAX( Table7[Date] ) &&
Table7[ContractIndex] <= MAX( Table7[ContractIndex] )
)
) RETURN IF( __value > 3500, "Y" )
parry2k thank you. I have adjusted the measure, however it is taking forever to run so I am not sure this solution will work for me if it continues to take this long.
Anonymoustry following changes and see if it performs better
Flagged = VAR __date = MAX( Table7[Date]) VAR __contractIndex = MAX( Table7[ContractIndex] ) VAR __value = CALCULATE( SUM( Table7[$ Value] ), ALLEXCEPT(Table7, Table7[Customer] ), Table7[Date] <= __date, Table7[Contract] <= __contractIndex ) RETURN IF( __value > 3500, "Y" )
parry2k unfortunately no improvement on performance with the updated measure