Link to table with ranges to pick up targets

[ConnieMaldonado]

Hello - 

I am using an employee's tenure in months to set targets for efficiency.

3 efficiency measures are being calculated, and there are different targets for each.  Let's call them: Efficiency Measure 1, Efficiency Measure 2, and Efficiency Measure 3

I created the following table in Power BI to avoid endless IF or SWITCH statements to pick up the correct efficiency target.  Also makes it easier when things change..  The "No" column is so I can sort correctly when I display the targets in a visual.

No	Range Start	Range End	Tenure Bucket	Efficiency Measure	Efficiency Target
1	0	3	0-3 Mos	Efficiency Measure 1	0.65
2	3	6	3-6 Mos	Efficiency Measure 1	0.75
3	6	12	6 mos-1 yr	Efficiency Measure 1	0.95
4	12	24	1-2 yrs	Efficiency Measure 1	1
5	24	36	2-3 yrs	Efficiency Measure 1	1
6	36		3 yrs +	Efficiency Measure 1	1
1	0	3	0-3 Mos	Efficiency Measure 2	0.65
2	3	6	3-6 Mos	Efficiency Measure 2	0.75
3	6	12	6 mos-1 yr	Efficiency Measure 2	0.95
4	12	24	1-2 yrs	Efficiency Measure 2	1
5	24	36	2-3 yrs	Efficiency Measure 2	1
6	36		3 yrs +	Efficiency Measure 2	1
1	0	3	0-3 Mos	Efficiency Measure 3	0.8
2	3	6	3-6 Mos	Efficiency Measure 3	0.9
3	6	12	6 mos-1 yr	Efficiency Measure 3	0.9
4	12	24	1-2 yrs	Efficiency Measure 3	0.9
5	24	36	2-3 yrs	Efficiency Measure 3	0.9
6	36		3 yrs +	Efficiency Measure 3	0.9

If I have a table by date, employee, Line of Business with the tenure in months calcuated for each employee, how do I link to this table to get the correct target  - should I use a filter?

Date	Employee ID	Tenure in Months	Line of Business
1/1/2022	123456	27	LOB1
1/1/2022	345678	8	LOB1

For example, if an employee's tenure is 27 months, and I'm looking at Efficiency Measure 1 (which is selected on the page), the target is 1.

Thanks in advance for any help.

[ConnieMaldonado]

Awesome.  Thank you.

[jgeddes · Accepted solution]

You can tackle this with a measure using a combination of CALCULATE with filters and LOOKUPVALUE.
It would look something like

Efficiency Target Lookup =

var _selectedMeasure =

// get the filtered efficiency measure

SELECTEDVALUE(targetTable[Efficiency Measure])

var _tenureValue =

// get the tenure in months for the selected line

SELECTEDVALUE(tenureTable[Tenure])

var _tenureBucket =

// get the tenure bucket for the selected efficiency measure and within tenure range start and end

CALCULATE(MAX(targetTable[Tenure Bucket]), FILTER(targetTable, targetTable[Efficiency Measure] = _selectedMeasure && _tenureValue >= targetTable[Range Start] && _tenureValue <= targetTable[Range End]))

Return

// get the efficiency target using the calculated tenure bucket and the selected efficiency measure

LOOKUPVALUE(targetTable[Efficiency Target], targetTable[Efficiency Measure], _selectedMeasure, targetTable[Tenure Bucket], _tenureBucket)

There is an issue with this method with the Range Start and Range End fields however.

In order for this to work properly a Range Start cannot equal a Range End from the previous row. 

E.g. 0-3 Months can be Range Start = 0 and Range End =3 but 3-6 Months would then need to have Range Start = 4 and Range End = 6. Also the 3+ Range End would have to have some value in it such as 999.

But using the data provided I ended with

Hopefully this gets you pointed in the right direction.