Forum Discussion
Checking multiple columns against a variable
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 , 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.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.
