Filter drop down B by drop down A

[jsingelais]

I have a SharePoint list (Intake Directory) with the following fields connected to my PowerApp:

Department (drop down - datacardvalue19)

Executive Sponsor (text)

Project Sponsor (drop down)

I want the user to select a value from the Department drop down and have the corresponding Executive Sponsor value auto-fill and the Project Sponsor drop down values to only show those project sponsors associated to the Executive Sponsor that was selected.

I'm running into an issue getting the Project Sponsor field to filter the way I need it. I've tried this but it's not working:
Filter(Choices([@'Intake Directory'].Project Sponsor); Value in Filter([@'Intake Directory'],DataCardValue19.Selected.Value))

[#2]

There is no relation to filter off using that method unless you have department in the choices filed for each sponsor and then each spouse name listed per line in project sponsor and even then it probably wouldn’t work right ?

Ideally you would keep two more lists somewhere that has the relationships so you can filter by one column and display the other and so on. Or you could use one list technically and have a drop down for relationship type in the list but either way need to have a place to define these relationships if that makes sense.

[#3]

@Chris Webb I'm not sure what you mean by "there is no relationship to filter off". I have this working on another PowerApp where when a user makes a selection in a drop-down field, two other text fields auto-fill based on what is displayed in a single SharePoint list.

Here is an example of what my list looks like for the current app:

Department	Executive Sponsor	Project Sponsor
HR	John Smith	Jane Doe Mike Smith
ITS	Tom Johnson	Michelle Jones Chris St. John

On my app if a user selects ITS as the department, then the Executive Sponsor should auto-fill to Tom and the Project Sponsor drop-down should only show Michelle & Chris.

[#4]

Oh, so this is a separate list from where the app stores data? I was under the impression that it was a single list with "choice" fields with those values, my bad :P.

[#5]

Something like this should work, if I understand the ask.

Department (drop down - datacardvalue19)
Items = 'Listname'.Department

Executive Sponsor (text)
Text = Lookup('Listname',Department = datacardvalue19.selected.value).Executive Sponsor (maybe a .DisplayName if this is a people column)

Project Sponsor (drop down)
Items = Filter('listname',Department = datacardvalue19.selected.value)

[#6]

It seems like that should work...but it's not. I'm still getting an invalid argument type error on the Project Sponsor drop down.

[#7]

put a .ProjectSPonsor at the end, whatever the column name is.