Checking multiple columns against a variable

[Desyn]

I have the following table

Employee	Week 1	Week 2	Week 3	Week 4	Standard
Jack	40	40	40	40	40
Tommy	40	40	32	32	32
Amy	25	25	25	25	25
Steve	42	42	40	40	40

I need to create a column which checks every "Week" column against the "Standard" column, whereby if any of the values in the Week columns are lower than than the Standard value, the row in marked as non-compliant.

In the example above, the resulting column would look like this:

Employee	Week 1	Week 2	Week 3	Week 4	Standard	Compliant
Jack	40	40	40	40	40	Yes
Tommy	40	40	32	32	32	No
Amy	25	25	25	25	25	Yes
Steve	42	42	40	40	40	Yes

• Jack is compliant because all his weeks are equal to, or greater, than the value in the standard column
• Tommy is NOT compliant because one of more of his weeks are lower than the value in the standard column
• Amy is compliant because all her weeks are equal to, or greater, than the value in the standard column
• Steve is compliant because all his weeks are equal to, or greater, than the value in the standard column

Please note - Steve is the only example where he has weeks greater than the standard value - this is fine, as long as no weeks are UNDER the standard value, the result should be compliant.

Thanks, Desyn.

[kleigh]

In Power Query, the following should work:

List.Accumulate(
{[Week 1],[Week 2],[Week 3],[Week 4]},
true,
(state, current) => state and current >= [Standard]
)

[Desyn]

Thanks @kleigh 

[Desyn]

Does anyone else have any ideas as I'm still stuck. Thanks.

[amitchandak]

@Desyn , Create a new column like

Column = if(SUMX({[Week 1],[Week 2],[Week 3],[Week 4]}, If( [Value] <>[Standard],1,0))=0 ,"Yes", "No")

[Desyn]

Hi @amitchandak,

I'm not sure that will work as it will produce compliance even if the columns were as follows:

Employee	Week 1	Week 2	Week 3	Week 4	Standard
Jack	40	42	38	40	40

The sum of the columns will average 40, even though one column is below 40.

[kleigh · Accepted solution]

It's using SUMX which works one value at a time, and sums the expression given, not the raw value.
The expression says "if non-compliant, output a 1 otherwise 0". When we sum that, if it is 0 then all of the values must have been compliant.

Since you allow hours to be over, the check should be [Value] < [Standard] but the principle works.

[Desyn]

That's great, thanks @amitchandak.

My mistake, I hadn't appreciated the SUMX function. This works perfectly, it's a very neat and tidy solution, I thought I'd need to do more than one operation.