Calculate(Sum([PRODUCTION_HOURS])) not with a filter but with a value limiter

[Anonymous]

I have a calculated column

c.SHIFT_PROD_HOURS = CALCULATE(SUM(CO_PROD_INFO[PROD_HRS_MOD]),CO_PROD_INFO[MFG_TYPE]="INJECTION")

I need to sum PROD_HRS_MOD. My problem is that when our ERP system goes down but the machines are still running that production time get rolled into whatever shift we're in when the system comes back online. This leads to values like 14.5 hours of production in a single shift, which is impossible because we run three eights.

The solution my production manager came up with that I am now trying to recreate in BI is to cap production hours at 8, ignore the extra hours, and record the shift where the database was down as "N/A"

So, I can't just filter out rows with PROD_HRS > 8. Rather, I need something that replaces any value over 8 with 8. I can't implement this higher in the data pipeline because I need the real PROD_HRS values for other calculations.

[vapid128 · Accepted solution]

ADD COLNUM

min(CO_PROD_INFO[PROD_HRS_MOD],8)

[Anonymous]

I'm guessing there's an additonal assertion here that your solution requires a second column? Seems like it works, but I'd love a cleaner solution if one existed.

Thank you