Drop-down menu

[francesco_canno]

HELLO
in a table called service I have a drop-down menu that takes data from another table ref. (TEST)
how can I not see the services I have already added?
THANK YOU

[Aleksi]

Do you have a Valid_If formula that filter the list… or do you have slice or security filter?

[francesco_canno]

Sorry, but I did not understand
The drop-down menu takes the values from another table with the REF function

[LeventK]

@francesco_cannone
What @Aleksi wants to say is, there are a couple of ways to filter a REF dropdown content.

1.) Using a Slice: You can create a slice and slightly change your REF_ROWS expression in the [Related …] column to read the values from this slice,

2.) Using a Security Filter: You can set a Security Filter for you ref table which will filter away the rows that you don’t want to display

3.) Using a Valid_if Expression: You can use a Valid_if expression in your REF column, to filter away ref rows i.e. SELECT(ParentTableName[Key],{Y/N condition})

Hope it’s clear now.

[francesco_canno]

I think the number 3 option is the best
I would like to do something like this in the picture

[LeventK]

@francesco_cannone
Is your ref table a READ_ONLY table? Because there is no +New link on top of your dropdown to add a new ref record. Not a bid deal but just asking.

[francesco_canno]

yes exactly is read only!

[Steve · Accepted solution]

I’m going to guess what you mean is that you want the drop-down menu to only display entries from the referenced table that haven’t already been referenced in the same column of other rows of this table.

In the Valid_If for the column:

FILTER("ThatTable", TRUE)
- SELECT(
 ThisTable[ThisColumn],
 ([KeyColumn] <> [_THISROW].[KeyColumn])
)

where ThatTable is the name of the referenced table, ThisTable and ThisColumn are the names of the table and column you want the drop-down menu in, and KeyColumn is the name of the key column for ThisTable

1. FILTER(...) gets a list of all key column values from ThatTable.

2. SELECT(...) gets a list of existing values in the ThisColumn column of ThisTable that…

3. ([KeyColumn] <> [_THISROW].[KeyColumn]) excludes the current row’s reference to ThatTable. If we don’t do this, the column value will be considered invalid if you edit the row again in the future.

4. FILTER(...) - SELECT(...) removes all of the references that occur in ThisTable (the “used” references) from the list of all possible references, leaving only those that are unused.

See also: FILTER(), SELECT()

[francesco_canno]

Thank you
It works perfectly !!!

[LeventK]

@francesco_cannone
SELECT(ParentTable[Key],NOT(IN([ThisTableRefColumn],ParentTable[Key])))

[Nick_Gemmell]

I am a newbie with no experience and I was wondering what the easiest way to create a drop down within a selection in the app would?
I have done what is is said in the tutorial but I must have missed something.