PowerApps cascading dropdown menus will allow you to make great apps for your users.
These can be difficult to set up for the first time, but using this feature makes dynamic filtering of menus through selection more accessible to users. By taking the time to create these menus, your titles will always correspond to one another.
We will make a simple application by pulling data out of an excel workbook to demonstrate the mechanics of cascading dropdowns. After working through these steps, you can apply them to your own data sets.
Want to follow along with our video? Watch as Shane guides us through Cascading Dropdowns in PowerApps:
Create a New Empty Application
Begin by signing into PowerApps. After the application loads, click on the ‘Apps’ button in the left pane. Click on ‘Create an App’ in the upper right. For now, just select a blank app with the phone layout.
Give your new application a few seconds to load. You can skip the tutorial this time.
Create Your Labels and Buttons
You should now have an empty application with the selected layout.
To create interactive labels with dropdown options:
Create a Label: Choose ‘Label’ from the top ribbon. Type in a name for your application. For this tutorial, we will use “Cascading Dropdowns.” You can format the text, change its size or color, and bold, italicize, or underline.
Create a Dropdown: Go ahead and add another label beneath the first one. Name it “Department.” Click ‘Controls’ in the top ribbon and select ‘Dropdown.’ Move your new dropdown beneath the label you just created. Rename the dropdown ‘DropDown_Dept.’
Create a Second Dropdown: Now, make another label and name it “Job Titles.” Create another dropdown and insert it beneath that label.
Your application will display the newly created labels. You should be able to interact with them, but they will be empty.
Add a New Data Source
With these two pieces in place, we now need to create a data connection to populate the menus.
Load up your data sheet and check it over. If you make the first values blank, no options will be selected by default. This will look better for the user because it displays empty text instead of the first items in the list.
Populate Your Label With Data
Click ‘View’ in the top ribbon followed by ‘Data sources.’ Now, click on ‘Add data source’ in the window that pops up. This will show a list of connections to import your data source from. Services including OneDrive, SharePoint, and Office 365 are available. Simply click the ‘New connection’ button to add a service provider to import your source from.
After making your selection, you will be able to import the data and select the table that you would like to use. It is important to note that you have to have a table already created within your data source for this process to work correctly.
Select the dropdown beneath the Department label that you previously created. Type ‘Distinct(JobTitles,Department)’ into the text box. Now, if you click on the preview you should see the department data populating the department box. However, you’ll notice that the information isn’t in alphabetical order.
Change the text to ‘Sort(Distinct(JobTitles,Department),Result)’ and press the preview once again. Now, the departments should be alphabetized.
Display the Job Titles
Next, we want the application to display the job titles for the chosen department. Select the dropdown beneath the “Job Titles” label then type ‘Filter(JobTitles,Department=DropDown_Dept.Selected.Value)’ into the function box at the top.
If you press the preview now, you will see that the job titles are populated based on which department is selected. However, the system only displays the department so far, not the positions themselves.
Change the code to ‘Distinct(Filter(JobTitles,Department=DropDown_Dept.Selected.Value),JobTitle)’ and run the preview again. PowerApps should now show the individual job titles when the department is selected.
Create Reset and Refresh Buttons
We will now create a couple buttons to make it easier to import new data:
Create a Reset Button: Select ‘Insert’ from the top ribbon. Then, choose the button from the available options. This will add a button to your project. Place it wherever you want it. Rename the button ‘Reset’ and type ‘Reset(DropDown_Dept);Reset(Dropdown2)’ into the top text bar.
Create a Refresh Button: Click ‘Insert’ and select the ‘Icons’ option. Choose an icon to add next to the reset button. Now, type ‘Refresh(JobTitles)’ into the text at the top.
These buttons are great for helping you work through any issues you might come across when importing and working with your data. They will allow you to reset the dropdown menus to their default settings any time. This is good for troubleshooting, but it can also be useful for end users.
Edit the Dropdown Display Mode
You might notice that you can click on the job titles before selecting a department. This presents no options, so it’s not a big deal, but to make the interface look cleaner we should add one last piece of code.
Click the arrow next to the box that says ‘Items’ and scroll down to select ‘Display Mode’ from the available options. Type ‘If(DropDown_Dept.Selected.Value = “”,DisplayMode.Disabled, DisplayMode.Edit)’ into the box.
Now, ‘Job Titles’ cannot be selected until you have chosen an option from the list of departments.
Easily Access Your Data with Cascading Dropdowns
You should now have your data dynamically loading in the cascading drop down menus. This feature allows you and your users to easily access the information.