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

Anonymous — Fri, 10 Jun 2022 19:19:41 GMT

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.

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

vapid128 — Fri, 10 Jun 2022 19:21:50 GMT

ADD COLNUM

min(CO_PROD_INFO[PROD_HRS_MOD],8)

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

Anonymous — Fri, 10 Jun 2022 19:35:42 GMT

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

topic Re: Calculate(Sum([PRODUCTION_HOURS])) not with a filter but with a value limiter in DAX Commands and Tips

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

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

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