Cascading Dropdown Functionality in Power apps

Creating cascading dropdowns in Power Apps enhances user interaction by filtering options based on previous selections. Using controls like Dropdowns and Combo Boxes, you can link data from sources like SharePoint lists.

Creating cascading dropdowns in Power Apps can significantly enhance the user experience by filtering options based on previous selections.

Here’s a step-by-step guide to help you set up this.

Perquisites

Use a SharePoint list or any other data source to store your hierarchical data (Master Data).

I am going to demonstrate cascading dropdown functionality using the following controls.

1. Dropdown Control
2. Combo box Control
3. Combo box with text box control

Dropdown Control

I used a Dropdown control to create a cascading effect. Based on the selected value in the Segment dropdown, the Country dropdown will be filtered. Then, based on the selected value in the Country dropdown, the Product dropdown will be filtered. Finally, based on the selected value in the Product dropdown, the Sale Price textbox will be set.

Note. Source Master lists all columns as single-line text.

Destination Try list structure is as follows: all columns single line text except Sale Price Currency Data type.

1. Segment Dropdown Configuration
   • Item Property

     Distinct('Financial Dummy data',Segment)

   • This sets the items in the Segment dropdown to be distinct values from the ‘Segment’ column in the ‘Financial Dummy data’ table.
   • OnChange Property

     Reset(ddCountry);Reset(ddProduct);Reset(txtSalePrice)

   • When the Segment dropdown value changes, it resets the Country dropdown, Product dropdown, and Sale Price textbox to their default states.
   • Update Property

     ddSegment.Selected.Value

   • This updates the Segment dropdown with the selected value.
2. Country Dropdown Configuration
   • Item Property

     Distinct(
     
         Filter(
     
             'Financial Dummy data',
     
             Segment = ddSegment.Selected.Value
     
         ),
     
         Country
     
     )

   • Update Property

     ddCountry.Selected.Value

3. Product Dropdown Configuration
   • Item Property

     Distinct(
     
         Filter(
     
             'Financial Dummy data',
     
             Segment = ddSegment.Selected.Value && Country = ddCountry.Selected.Value
     
         ),
     
         Product
     
     )

   • Update Property

     ddProduct.Selected.Value

4. Sale Price Text box Configuration
   • Default Property.
   • The LookUp function in Power Apps is used to find the first record in a table that matches a specified formula.

     LookUp(
     
         'Financial Dummy data',
     
         Segment = ddSegment.Selected.Value && Country = ddCountry.Selected.Value && Product = ddProduct.Selected.Value,
     
         ' Sales'
     
     )

2. Combo box Control

Let's see how to do Cascading dropdown using combo box control, code will remain the same only the control will change in this case.

Note. The database master table named VisTbl_Country_States has the table schema below from which we are fetching cascading data.

1. Country Combo Box Configuration
   • Item Property: This property lists all distinct country names from the VisTbl_Country_States table.

     Distinct(
     
         VisTbl_Country_States,
     
         country_name
     
     )

   • Update Property: This property updates the selected value of the country combo box.

     cbxCountry.Selected.Value

2. States Combo Box Configuration
   • Item Property: This property lists all distinct state names from the VisTbl_Country_States table, filtered by the selected country.

     Distinct(
     
         Filter(
     
             VisTbl_Country_States,
     
             country_name = cbxCountry.Selected.Value
     
         ),
     
         name
     
     )

   • Update Property: This property updates the selected value of the states combo box.

     cbxStates.Selected.Value

3. Latitude Text Box Configuration
   • Default Property: This property looks up the latitude value from the VisTbl_Country_States table based on the selected country and state.

     LookUp(
     
         VisTbl_Country_States,
     
         country_name = cbxCountry.Selected.Value && name = cbxStates.Selected.Value,
     
         latitude
     
     )

4. Longitude Text Box Configuration
   • Default Property: This property looks up the longitude value from the VisTbl_Country_States table based on the selected country and state.

     LookUp(
     
         VisTbl_Country_States,
     
         country_name = cbxCountry.Selected.Value && name = cbxStates.Selected.Value,
     
         longitude
     
     )

5. Data Table Filter
   • Item Property: This property filters the data table based on the selected country and state. If no country or state is selected, it shows all data.

     Filter(
     
         VisTbl_Country_States,
     
         cbxCountry.Selected.Value = Blank() Or country_name = cbxCountry.Selected.Value &&
     
         cbxStates.Selected.Value = Blank() Or name = cbxStates.Selected.Value
     
     )

3. Combo box with text box control

Here I use the combo box to best fit to search among 195 countries from the data source.

The rest of Capital & Currency are text filed where auto fetch value will be saved based on country selection

Master List Structure

All columns are single-line text.

1. Combo Box Configuration
   • Item Property

     Distinct('Countries_capitals & their currency', Country)

   • This code shows distinct country values.
   • Update Property

     cbxCountry1.Selected.Value

2. Capital Text Box Configuration
   • Default Property

     LookUp('Countries_capitals & their currency', Country = cbxCountry1.Selected.Value, Capitals)

   • This sets the default value to the capital based on the selected country.
   • Update Property

     txtCapital.Text

3. Currency Text Box Configuration
   • Default Property

     LookUp('Countries_capitals & their currency', Country = cbxCountry1.Selected.Value, Currency)

   • This sets the default value to the currency based on the selected country.
   • Update Property

     txtCountry.Text

4. Precautionary Note
   • Save the form data to the destination data source as a single-line text column type to avoid data type conversion issues.
   • If the “Allow Searching” option keeps turning off automatically, save your Power Apps, close it, and then reopen it, this will fix the issue.

Thanks for reading the blog.

I hope this makes it clearer!