How to use row field value in table lookup?

[BenGWeeks]

Hi there,

How do I use the current field to do a lookup?

[v-kelly-msft · Accepted solution]

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]

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)