Forum Discussion

BenGWeeks's avatar
BenGWeeks
Regular Visitor
5 years ago
Solved

How to use row field value in table lookup?

Hi there, How do I use the current field to do a lookup?    
General Comment
Need Help
Tips and Tricks
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  BenGWeeks ,

    If [Area] is a determined value,

    Try below measure:

    AVERAGE=CALCULATE(AVERAGE(DevOpsAssessmentResponses[Area]),FILTER(ALL('DevOpsAssessmentResponses'),DevOpsAssessmentResponses[Area]="XXXXX"))

    If [Area] is dynamic,

    Try below measure:

    AVERAGE=CALCULATE(AVERAGE(DevOpsAssessmentResponses[Area]),FILTER(ALL('DevOpsAssessmentResponses'),DevOpsAssessmentResponses[Area]=MAX(DevOpsAssessmentResponses[Area]))

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
5 years ago

Use SELECTEDVALUE() or MIN() / MAX() etc depending if you care about totals being meaningful.

 

You need to capture SELECTEDVALUE before you do CALCULATE(), by assigning a variable. below example illustrates the point but is a bit useless as the "current row"  is already in the context, so it is not necessary to filter like this.

 

AVERAGE = 

var a = SELECTEDVALUE(DevOpsAssessmentResponses[Area])
return CALCULATE(AVERAGE(something something),DevOpsAssessmentResponses[Area]=a)