Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

2 REPLIES 2
ConnieMaldonado
Responsive Resident
Responsive Resident

Awesome.  Thank you.

jgeddes
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors