cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kbig02
Helper I
Helper I

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 III
Super User III

Hi @kbig02 

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 III
Super User III

Hi @kbig02 

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors