Hi I have four tables:
- ProductFeature * intermediate table to handle the many to many relationship*
- Search captures the user selection of features
I am looking for a way where I can search for a product that has ALL the features selected not just one or some. The IN () function is in fact an OR function where it returns products that have one or more of the features in the list.
Normally: when looking for example: 3 selected features.
Sort(Select(ProductFeature[Product], (IN( [FeatureUUID], [_THISROW].[Features])) ,TRUE))
returns Result = ETH-USD, ETH-USD Swap
which is incorrect for my purpose as ETH-USD Swap is missing one of the features.
The following select statement :
Result = Sort(Select(ProductFeature[Product], (IN( [FeatureUUID], [_THISROW].[Features])) ,FALSE))
returns the following: when looking for 3 selected features.
ETH-USD , ETH-USD , ETH-USD , ETH-USD Swap , ETH-USD Swap
Only one product in the results returned has all three features, because it appears 3 times. Is there a way to write the select statement to return only products that when counted appears 3 times, matching the number of features selected, inferring that the product has all 3 features?
So the end result would be Result = ETH-USD.