Exclude e certain value from a Reference Dropdown

[sorin_mihai]

Hello,

I have a Category Table and a Projects Table.
The Projects table has a column named Project Category, which references the Category Table.
There are three rows in the Category Table and so in the Form of a project, the Project Category Dropdown displays these three values.
How can I make this dropdown display only two of these values? without erasing the row from the Category Table itself.

I tried putting this in the Valid if of the Projects Category column but it doesn’t work…:
[Project Category]<>“ValueNotWanted”

Thank you,
Sorin

[LeventK · Accepted solution]

@sorin_mihai
Valid_if

SELECT(Projects[Key], NOT([Project Category]=“ValueNotWanted”))

[sorin_mihai]

Thank you

[LeventK]

You’re very welcome

[LeventK]

@sorin_mihai
Also provided the unwanted value in not falling into the middle when you sort that column in ascending or descending order, you can also use one of these:

TOP(
	ORDERBY(
		SELECT(Projects[Key],TRUE,TRUE), //cosntruct the list
		[Project Category], //as per this column
		FALSE //sort in ascending order
	),
	2 //return top 2 values
)

OR

TOP(
	ORDERBY(
		SELECT(Projects[Key],TRUE,TRUE), //construct the list
		[Project Category], //as per this column
		TRUE //sort in descending order
	),
	2 //return top 2 values
)

[SteffenMauthe]

Is it also possible to exclude it only in the drop-down menu when adding/editing a column but not as ValidIf?
e.g. the category was valid but in the past but is for further projects not valid any more, so I want to exclude it from the dropdown, but there exist old projects with this category.

[Steve]

Something like this for Valid if?

(
  SELECT(
    Projects[Category],
    ("Active" = [Status])
  )
  + LIST([_THISROW_BEFORE].[Category])
  - LIST("")
)

SELECT(...) would be whatever expression you'd use to construct the list of current valid choices.

+ LIST([_THISROW_BEFORE].[Category]) adds the row's current value of the Category column to the list of valid values, ensuring it occurs even if the list of current valid choices (from SELECT(...)) doesn't include it. [_THISROW_BEFORE] is used to refer to the value the row had coming into the change, which won't change should the user choose something else while in the form.

- LIST("") removes any blank Category values that may occur, and removes duplicates as a side-effect.

[SteffenMauthe]

But this will then also show the "new" invalid columns as drop-down for new projects?

[Steve]

The expression I provided will only show valid values for new rows. For existing rows only, it will show valid values plus the existing row's current value (even if now invalid). This allows you to update existing rows without having to change the value that was valid when added but is no longer valid. Try it and see what happens.

[SteffenMauthe]

Works perfect. Thanks a lot 🙂