Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Measure To Return Value based on multiple criteria from another table

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

SalesRegionTeam
BobbyWestTeam 2
JohnEastTeam 2
RobertSouthTeam 2
VicenzioWestTeam 3
RobertEastTeam 3
MelissaSouthTeam 9
MichaelEastTeam 5
NancySouthTeam 4

 

 

Goals

TeamRegionMetricGoal
Team 2WestGas150
Team 2SouthSales1500
Team 3EastGas150
Team 3WestSales2000
Team 4SouthGas200
Team 4WestSales1600
Team 5EastGas175
Team 5SouthSales1500
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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