Look up value in one column from one table to indicate if it's between two values from another table

[5foot9]

I have two tables related by slot number called slot range and slot details.
Slot range contains unique slot numbers with minimum range and maximum range columns.
Slot details contains multiple slot numbers with total column
I want a calculated column in slot details to indicate if a slot's total is within its minimum and maximum range.
I have tried the following and have found that AND can have only two arguments. I have tried different combination of concatenating using && to no avail.


=IF(AND(VALUES(Details[Slot])=VALUES(Range[Slot]),VALUES(Details[Total])>=VALUES(Range[Min]),VALUES(Details[Total])<=VALUES(Range[Max])),"yes","no").


A solution to my problem or a nudge in the right direction would be very much appreciated.

 

[Marcelo Branco]

Try something like this

Table Range in A1:B6
Table Details in E1:G8

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Slot​	Min​	Max​		Slot​	Total​	Result​
2​	1​	1​	10​		1​	15​	No​
3​	2​	2​	20​		1​	8​	Yes​
4​	3​	3​	30​		4​	40​	Yes​
5​	4​	4​	40​		3​	45​	No​
6​	5​	5​	50​		2​	1​	No​
7​					5​	48​	Yes​
8​					2​	16​	Yes​
9​

<tbody>
</tbody>

Formula in G2
=IF(AND([@Total]>=INDEX(Range[Min],MATCH([@Slot],Range[Slot],0)),[@Total]<=INDEX(Range[Max],MATCH([@Slot],Range[Slot],0))),"Yes","No")

Hope this helps

M.

 

[Marcelo Branco]

I saw this question when searching in Zero Reply Posts. I think it should have been posted in Excel Questions forum.

M.

 

[5foot9]

I'm really sorry, I seem to have missed out a key piece of information! I assumed by posting in the PowerBi section people would naturally assume I am trying to get an answer for a calculated column in a powerpivot datamodel, not in excel itself. Sorry again if I've wasted anyone's time.

The table above is exactly what I'm after but in two separate tables that are related by 'slot' but using DAX formula

 

[Marcelo Branco]

No worries.
My bad. I hadn't noticed that the question is about Power BI
Maybe someone else can help you.

M.