Forum Discussion
Checking multiple columns against a variable
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.
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.
7 Replies
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")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.
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.
In Power Query, the following should work:
List.Accumulate(
{[Week 1],[Week 2],[Week 3],[Week 4]},
true,
(state, current) => state and current >= [Standard]
)Does anyone else have any ideas as I'm still stuck. Thanks.
