Forum Discussion
Advanced Calculated Column Evaluation with Variable Values, Keys, Operators, and Operands
Is it possible to write something in M Query that evaluates a score based on the distinct key in the same row as the score against a key table that shows all of the associated operators and operands and the resulting value?
This is the logical evaluation table I put together in excel.
So if there is key value and score value in a different table, can M Query take the key and score values, lookup to get the associated parameters that match the key in the table below?
Example:
| Key | Score |
| A2 | 4 |
If I run the above score against the matching row in the table below, the result should be 1 (Green Result).
| Key | Score | Red Operator | Red Operand | Red Result | Key | Score | Yellow Min Operator | Yellow Min Operand | Key | Score | Yellow Max Operator | Yellow Max Operand | Yellow Result | Key | Score | Green Operator | Green Operand | Green Result | ||||||||||||||||||||||
| if | [Key] | = | A1 | and | [Score] | >= | 1 | then | 3 | else if | [Key] | = | A1 | and | [Score] | >= | 0 | and | [Key] | = | A1 | and | [Score] | <= | 0 | then | 2 | else if | and | [Key] | = | A1 | and | [Score] | = | 0 | then | 1 | ||
| [Key] | = | A2 | and | [Score] | >= | 8 | then | 3 | else if | [Key] | = | A2 | and | [Score] | >= | 5 | and | [Key] | = | A2 | and | [Score] | <= | 6 | then | 2 | else if | and | [Key] | = | A2 | and | [Score] | < | 8 | then | 1 | |||
| [Key] | = | A3 | and | [Score] | >= | 8 | then | 3 | else if | [Key] | = | A3 | and | [Score] | >= | 70 | and | [Key] | = | A3 | and | [Score] | <= | 79 | then | 2 | else if | and | [Key] | = | A3 | and | [Score] | < | 8 | then | 1 |
Hi Anonymous
Is the lookup table a 1-row only table? If so
if Table1[Key]{0} = "A1" and Table1[Score]{0} >= 1 then 3 else if Table1[Key]{0} = "A1" and etc....
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
1 Reply
Hi Anonymous
Is the lookup table a 1-row only table? If so
if Table1[Key]{0} = "A1" and Table1[Score]{0} >= 1 then 3 else if Table1[Key]{0} = "A1" and etc....
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers