cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ConnieMaldonado
Responsive Resident
Responsive Resident

Link to table with ranges to pick up targets

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.

 

NoRange StartRange EndTenure BucketEfficiency MeasureEfficiency Target
1030-3 MosEfficiency Measure 10.65
2363-6 MosEfficiency Measure 10.75
36126 mos-1 yrEfficiency Measure 10.95
412241-2 yrsEfficiency Measure 11
524362-3 yrsEfficiency Measure 11
636 3 yrs +Efficiency Measure 11
1030-3 MosEfficiency Measure 20.65
2363-6 MosEfficiency Measure 20.75
36126 mos-1 yrEfficiency Measure 20.95
412241-2 yrsEfficiency Measure 21
524362-3 yrsEfficiency Measure 21
636 3 yrs +Efficiency Measure 21
1030-3 MosEfficiency Measure 30.8
2363-6 MosEfficiency Measure 30.9
36126 mos-1 yrEfficiency Measure 30.9
412241-2 yrsEfficiency Measure 30.9
524362-3 yrsEfficiency Measure 30.9
636 3 yrs +Efficiency Measure 30.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?

 

DateEmployee IDTenure in MonthsLine of Business
1/1/202212345627LOB1
1/1/20223456788LOB1

 

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.

1 ACCEPTED SOLUTION
jgeddes
Solution Sage
Solution Sage

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
jgeddes_0-1665087681992.png

 


Hopefully this gets you pointed in the right direction.

View solution in original post

2 REPLIES 2
ConnieMaldonado
Responsive Resident
Responsive Resident

Awesome.  Thank you.

jgeddes
Solution Sage
Solution Sage

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
jgeddes_0-1665087681992.png

 


Hopefully this gets you pointed in the right direction.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.