How to remove specific values from dependent dropdown after one selection of employee?

[Surdusvacca]

Hi,

I have 3 tables.

• Medarbejdere
• Dropdowns
• Forbrug

Table 'Dropdowns' has 3 columns: Kategori, Type and Årsag

I use the table Dropdowns for dependent dropdowns in the Forbrug.

Everything is working just fine.

However, in the table Dropdowns for the kategori Målebånd one of the options (Årsag) are: Har aldrig haft et
And for the kategori Snow Spikes one of the options (Årsag) are: Har aldrig haft et par.

Example with Snow Spikes selected:

I would like when I select an employee (Medarbejder), I can only choose Har aldrig haft et and Har aldrig haft et par one each. Meaning these two options should either disappear or be greyed out if they have already been chosen on a given employee.

I have tried with help from ChatGPT creating a virtual column with this formula:
IN("Har aldrig haft et", SELECT(Forbrug[Årsag], AND([Medarbejder nr.] = [_THISROW].[Medarbejder nr.], IN([Årsag], LIST("Har aldrig haft et")))))

And another virtual column:
IN("Har aldrig haft et par", SELECT(Forbrug[Årsag], AND([Medarbejder nr.] = [_THISROW].[Medarbejder nr.], IN([Årsag], LIST("Har aldrig haft et par")))))


In the column 'Årsag' valid if field I enter this:
IFS(
OR(
[Har aldrig haft et],
[Har aldrig haft et par]
),
NOT(IN(DROPDOWNS[ÅRSAG], LIST("Har aldrig haft et", "Har aldrig haft et par"))),
TRUE,
TRUE
)

But it is not working for me.
With this setup, the field 'Årsag' is visible all the time (Dependent dropdown stops working) and also, even that the field 'Årsag' is visible, nothing is shown in the dropdown when I choose it. The list is empty.

Should I go another way to do this? 

[lynchk21 · Accepted solution]

In the column 'Årsag' valid_if you can try to enter:

ISBLANK(SELECT(Forbrug[Årsag], AND([Medarbejder nr.] = [_THISROW].[Medarbejder nr.],CONTAINS([Årsag],"Har aldrig haft et"))))

This should not allow the user to select any Årsag that has ""Har aldrig haft et" in its name if they are already in the Forbrug table with an Årsag that has ""Har aldrig haft et" in its name. It will still show as an option, but will show the valid_if error is they select it. 

[Surdusvacca]

Thanks a lot. I got it to work 🙂