Forum Discussion
Check if value is in range
Hello Guys.
looking around and read a lot of posts - but can't find one that fits my needs completely...
I have two tables, one called "structure" and another called "posts"
in the "structure" table I have following records:
key / description columns:
0
999 / Project
2000 / Service
3000 / warranty
4000 / invoiced
in the "post" table I then have a key column that could be any number - here I would like to add another column that gets a value from the "structure" table like:
key in "post" table is 225 - here I would like to get the value from the "structure" table where it's inside 0-999 meaning the value should be "Project"
another example where the key in "post" table is 2500 - here I would like to get the value from the "structure" table that says "Service" since it's between 2000 and 3000
Rasmus7700 the logic you explained doesn't make sense in the example. for 225, you want to take the value from 999 from the upper bound, whereas in another example for 2500, you want the value from the lower band. I think the logic should be identical in both cases, no?
Anyhow try using this new INDEX function and see if it gets you what you are looking for.
Description Column in Post Table = VAR __Post = 'Post'[Post] RETURN SELECTCOLUMNS ( INDEX ( 1, FILTER ( ALL ( 'Structure' ), 'Structure'[Key] <= __Post ), ORDERBY ( 'Structure'[Key], DESC ) , MATCHBY ( 'Structure'[Key] ) ), "@Desc", [Desc] )
3 Replies
Hi Rasmus7700
you can create a calculated column
Column = var __lookupKey = CALCULATE(MIN('structure'[key]), 'structure'[key] >= EARLIER('post'[key])) var __result = LOOKUPVALUE('structure'[description], 'structure'[key], __lookupKey) RETURN __resultMake sure, your post and structure table has no relationships
Rasmus7700 the logic you explained doesn't make sense in the example. for 225, you want to take the value from 999 from the upper bound, whereas in another example for 2500, you want the value from the lower band. I think the logic should be identical in both cases, no?
Anyhow try using this new INDEX function and see if it gets you what you are looking for.
Description Column in Post Table = VAR __Post = 'Post'[Post] RETURN SELECTCOLUMNS ( INDEX ( 1, FILTER ( ALL ( 'Structure' ), 'Structure'[Key] <= __Post ), ORDERBY ( 'Structure'[Key], DESC ) , MATCHBY ( 'Structure'[Key] ) ), "@Desc", [Desc] )Awesome - that one solved my problem like a charm - Thanks 🙂
