Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.