Value position withing two column ranges

[DavidMG82]

Thanks ahead for you advice on this. 

I have a variable that determines the year:

var _year = 2020

and one variable that collects a value:

var _headcount = 550

How can I use a formula in return, to take the 550 and search on another sheet called 'pools' and find the correct value in column "Pool", like in this example the result should be based on the year and value: Pool 'D'

year	pool	headcountMin	headcountMax
2020	A	5000
2020	B	2001	5000
2020	C	1001	2000
2020	D	501	1000
2020	E	251	500
2020	F	101	250
2020	G	1	100
2021	A	5000
2021	B	4001	5000
2021	C	2501	4000
2021	D	501	2500
2021	E	301	500
2021	F	201	300
2021	G	1	200

[barritown · Accepted solution]

Hi @DavidMG82,

Please try this measure:

In plain text:

Measure = 
VAR _year = 2020
VAR _headcount = 550
RETURN "Pool '" & MINX ( FILTER ( data, [year] = _year && _headcount >= [headcountMin] && _headcount < [headcountMax] ), [pool] ) & "'"

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[DavidMG82]

Works as expected, thanks Alexander.