How to Distinct Filter in CDS which replicates function in SharePoint

[ClarkyPA365]

Hi,

I have what i need working in Sharepoint for a Canvas application.  I'm trying to convert my Sharepoint list into CDS.

In SP i have a list called LocationVDepartement with two columns

Columns, Location & Department with all locations and the department that are within that Location

In the canvas application I have two drop downs boxes and when a certain location is selected from one then only those departments located there display in the other to choose from. I use the Distinct Filter formula.

In CDS do i need to create two separate Option sets?  If so how do i link them so the same process occurs?? 

[GarethPrisk]

It sounds like your data is relational. You likely have a list of Departments, and a list of Locations.
While the data could be setup as 1:N, meaning 1 Department has N Locations, or 1 Location has N Departments, it is probably more of a N:N scenario. Meaning a Location (physical) may have many Departments in it, but some of those same Departments are at other Locations.
In CDS, you should consider setting these up as Option Sets but you'll have to consistently enforce rules to interrelate them.
The alternative is to have two entities: Departments and Locations. This gives you the advantage of it being data (a record for each department/location), so you can extend metadata (details), and apply security, and interrelate the two.
To interrelate them, you can use an OOB N:N relationship but this has some shortcomings. There's no way to extend the metadata, and reporting (beyond Advanced Find) can be difficult. It's still viable, though.
The next option is a manual N:N, or intersect entity. You can call it Department Locations, or Location Departments depending on your business needs. This would have a lookup (N:1) to each entity, and requires some configuration (like Quick Create forms) and whatnot to make a good UX. However, it does allow you to define additional details about the relationship, and is a distinct entity to search and use in PowerApps.
You'll be able to build canvas app dropdowns, which seem like option sets to the users, using the depends on functionality as well.

[ClarkyPA365]

Hi @GarethPrisk 

Thanks for your reply.

I've attached a screen shot of how I have the Location / Departments set out in SP.

Then use the formula 

Sort(Distinct(LocationVDepartment,Title),Result) for the Location drop down 

Distinct(Filter(LocationVDepartment,Title=IDDropdown_Loc.SelectedText.Value),Department) for the Department drop down.

There is only one department, I shouldn't call them departments they are wards so only the one ward in a location.

So if I go down the two entity route how would this look to make a relationship??