Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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: 

KeyScore
A24

 

If I run the above score against the matching row in the table below, the result should be 1 (Green Result).

 

   Key ScoreRed OperatorRed Operand Red Result   Key ScoreYellow Min OperatorYellow Min Operand   Key ScoreYellow Max OperatorYellow Max Operand Yellow Result    Key ScoreGreen OperatorGreen Operand Green Result  
if[Key]=A1and[Score]>=1then3else if[Key]=A1and[Score]>=0and[Key]=A1and[Score]<=0then2else ifand[Key]=A1and[Score]=0then1  
 [Key]=A2and[Score]>=8then3else if[Key]=A2and[Score]>=5and[Key]=A2and[Score]<=6then2else ifand[Key]=A2and[Score]<8then1  
 [Key]=A3and[Score]>=8then3else if[Key]=A3and[Score]>=70and[Key]=A3and[Score]<=79then2else ifand[Key]=A3and[Score]<8then1  
1 ACCEPTED SOLUTION
AlB
Super User
Super User

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 

 

SU18_powerbi_badge

View solution in original post

1 REPLY 1
AlB
Super User
Super User

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 

 

SU18_powerbi_badge

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors