Getting data from two filters from Dataverse tables for a power apps dropdown

[Geeam]

I got two Dataverse tables, one having salesman name (field: Name) and the office (field: Location) he is assigned to. The other table having fields Year (field: Year) and office (field: Location), as every year some Locations are added or dropped. I want to populate items in a dropdown for Locations (based on the Year selected in another dropdown) and only assigned to the Name who is logging in.

How do I accomplish this in the best way!

[Amik]

@Geeam -

1. Please confirm the data types for the Name, Location, and Year fields (Single Line Text, LookUp, Local or Global Choice etc).
2. Could you share a sample of what both tables look like in terms of structure? In Excel for example.

[Geeam]

Thank you very much Imran for the prompt reply. All these fields are single line of Text which are either filled in by the user directly or through data transfer. The tables are not very big.

[Amik]

@Geeam - assuming the "Name" field has been populated with User emails, you can use:

Filter(
    'Your Salesman Table',
    Location = 'Your Location Dropdown'.Selected.Value,
    Name = User().Email
)

[Geeam]

Thanks again Imran for the suggested solution. I think I need to give more clarification of the environment.

two tables 

1) SALESMAN: Columns  ID(GUID), NAME(Single Line of Text) , LOCATION(Single Line of Text -showing all the locations Salesman Assigned to) and two other Columns MANAGER, TENURE which are not relevant here)

ID	NAME	LOCATION
---------GUID-----	USER().FULLNAME	CALIFORNIA

2) PERIOD: Columns  ID(GUID), YEAR(Single Line of Text) , LOCATION(Single Line of Text-- Values are same as in Sales) and two other irrelevant fields)

ID	YEAR	LOCATION
----GUID--	2024	CALIFORNIA

LOCATION values will be common in both the Tables.

There are two dropdowns:

1) Year - Distinct Year value will be loaded from the PERIOD Table (like 2024,2023,2022..)

2) Location-- Want all the Locations in this dropdown for the Year selected in the first dropdown to which the logging in Salesman is assigned in the first table.

Hope it will give some more clarification. Thanks again for the response.

[Akshansh-Sharma · Accepted solution]

Hi @Geeam ,

Thanks for clarifying the requirements can you please try this code on the items property

with({var_UserAssignedLocations: showcolumns(filter(Salesman, name=user().displayname),Location)},
filter(Period, year = YearDropDown.selected.value, Location in var_UserAssginedLocations))

One thing to consider in this code 

1. Name column can have duplicate values so you should add a column in your salesman table which can identify a user uniquely i.e. email or azure entra id of the user

but over all this should achieve what you are trying to do, let me know how it goes

---------------------------------------------------------------------------------------------------------------------------

Please click Accept as solution if my post helped you solve your issue. If the content was useful in other ways, please consider giving it Thumbs Up 😉

LinkedIn- https://www.linkedin.com/in/akshansharma/

[v-jefferni]

Hi @Geeam ,

Items of the LOCATION Combo box:

Distinct(Table({Location: LookUp(SALESMAN, NAME = User().FullName).Location}, Filter(PERIOD, Value(Year) = Value(YearComboBox.Selected.Value)).LOCATION),LOCATION)

Best regards,