Solved: Filtering the values in a list based on another ta...

provisrudd · 02-09-2022 03:10 PM

Im trying to make a valid_if list of values based on 2 columns ([Serial ID] & [Status]) in table "Scope Inventory"

[Status] has a list of values In Stock, Retired and Sent for Repair

valid_if AND(Scope Inventory[Serial ID],Scope Inventory[Status]=In Stock)

Error:Cannot compare List with Text in (Scope Inventory[Status] = "In Stock")

I'm trying to limit the choices to [Serial ID] that contain the [Status]=In Stock

WillowMobileSys

Valid_If can be used in two ways - a List of valid values or a Yes/No validation expression that verifies the input is valid.

The first issue is, as soon as you start with AND(), the expression is expected to be a Yes/No expression. This is not what you described you wanted.

The second issue is that AND() functions cannot operate on Lists. They operate on OTHER Yes/No expressions - such as IN(), OR(), "=", etc

So, for your expression to return the List of values you simply need to implement a proper SELECT():

SELECT(Scope Inventory[Serial ID],[Status] = "In Stock")

You might also check this article out:

WillowMobileSys

Valid_If can be used in two ways - a List of valid values or a Yes/No validation expression that verifies the input is valid.

The first issue is, as soon as you start with AND(), the expression is expected to be a Yes/No expression. This is not what you described you wanted.

The second issue is that AND() functions cannot operate on Lists. They operate on OTHER Yes/No expressions - such as IN(), OR(), "=", etc

So, for your expression to return the List of values you simply need to implement a proper SELECT():

SELECT(Scope Inventory[Serial ID],[Status] = "In Stock")

You might also check this article out:

provisrudd

Thank you for such a thorough and perfect solution. 🙂

Filtering the values in a list based on another table of 2 columns