Measure To Return Value based on multiple criteria from another table

[Anonymous]

I'm looking for a Measure that will look up a value in my Goals table based on multiple criteria.

So for Bobby, I would need to look up in the Goals table, by his Region, and then Team, and specific Metric, in this case "Gas", and it should return the value of 150.

I've tried different variations of filters, but still am unable to get it to work.  The 2 tables have a relationship linked by Team.  But I'm needing a measure to bring in the Value of the Goal for a second part of a calculation using VAR statements.

Sales Team

Sales	Region	Team
Bobby	West	Team 2
John	East	Team 2
Robert	South	Team 2
Vicenzio	West	Team 3
Robert	East	Team 3
Melissa	South	Team 9
Michael	East	Team 5
Nancy	South	Team 4

Goals

Team	Region	Metric	Goal
Team 2	West	Gas	150
Team 2	South	Sales	1500
Team 3	East	Gas	150
Team 3	West	Sales	2000
Team 4	South	Gas	200
Team 4	West	Sales	1600
Team 5	East	Gas	175
Team 5	South	Sales	1500

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _select=SELECTEDVALUE('Goals'[Metric])
return
CALCULATE(SUM('Goals'[Goal]),FILTER(ALL('Sales Team'),'Sales Team'[Sales]=MAX('Sales Team'[Sales])&&'Sales Team'[Region]=MAX('Goals'[Region])&&'Sales Team'[Team]=MAX('Sales Team'[Team])&&MAX('Goals'[Metric])=_select))

2. Use the [Metric] column of the [Goals] table as the slicer

3. Result:

Select the slicer as Gas, and the specified Sales will display the values under Team, Region and Gas.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly