Cascading dropdowns in Power Apps are a powerful feature used to create dependent dropdown lists, where the selection in one dropdown list filters the options available in another. This functionality is useful in scenarios where you need to display related data in a hierarchical manner, such as selecting a country to filter the list of states, or selecting a product category to filter the list of products.
Let’s get started by building our data structure. Each column is a single line of text.
And now onto our Power Apps. I have three dropdowns; the first one will have the Make, then Model and finally Fuel Type.
On our first column we’re going to use this PowerFx code:
Distinct('Cascading Data',Title)Breakdown of the Code
The code Distinct(‘Cascading Data’, Title) in Power Apps is used to extract a unique list of values from a specific column in a data source. Here’s a detailed breakdown of what this code does:
- Distinct Function:
- The Distinct function returns a one-column table that contains the unique values from the specified column of a data source.
- Syntax: Distinct(DataSource, ColumnName)
- ‘Cascading Data’:
- This is the name of the data source (e.g., a SharePoint list, a SQL table, or an Excel table) that you are working with.
- The data source should contain a column named Title.
- Title:
- This specifies the column from which you want to extract unique values.
- The column name should be a valid column in the ‘Cascading Data’ data source.
On our second dropdown we’re going to use this code:
Distinct(Filter('Cascading Data',Title = drpMake.Selected.Value),Model)Breakdown of the Code
The code Distinct(Filter(‘Cascading Data’, Title = drpMake.Selected.Value), Model) in Power Apps is used to filter a data source based on a selected value in a dropdown control and then extract unique values from a specific column in the filtered data. Here’s a detailed breakdown of what this code does.
- Filter Function:
- The Filter function is used to find records in a data source that meet certain criteria.
- Syntax: Filter(DataSource, Condition)
- ‘Cascading Data’:
- This is the name of the data source (e.g., a SharePoint list, a SQL table, or an Excel table) that you are working with.
- The data source should contain columns named Title and Model.
- Title = drpMake.Selected.Value:
- This is the condition used in the Filter function.
- It filters the data source ‘Cascading Data’ to include only those records where the Title column matches the selected value in the dropdown control named drpMake.
- drpMake.Selected.Value refers to the currently selected value in the dropdown control drpMake.
- Distinct Function:
- The Distinct function returns a one-column table that contains the unique values from the specified column of a data source.
- Syntax: Distinct(DataSource, ColumnName)
- Model:
- This specifies the column from which you want to extract unique values in the filtered data.
- The column name Model should be a valid column in the ‘Cascading Data’ data source.
And finally, on our third dropdown:
Distinct(Filter('Cascading Data',Title = drpMake.Selected.Value,Model=drpModel.Selected.Value),'Fuel Type')Breakdown of the Code
The code Distinct(Filter(‘Cascading Data’, Title = drpMake.Selected.Value, Model = drpModel.Selected.Value), ‘Fuel Type’) in Power Apps is used to filter a data source based on selections from two dropdown controls and then extract unique values from a specific column in the filtered data. Here’s a detailed breakdown of what this code does:
- Filter Function:
- The Filter function is used to find records in a data source that meet certain criteria.
- Syntax: Filter(DataSource, Condition1, Condition2, …)
- ‘Cascading Data’:
- This is the name of the data source (e.g., a SharePoint list, a SQL table, or an Excel table) that you are working with.
- The data source should contain columns named Title, Model, and Fuel Type.
- Title = drpMake.Selected.Value:
- This condition filters the data source to include only those records where the Title column matches the selected value in the dropdown control named drpMake.
- Model = drpModel.Selected.Value:
- This condition further filters the data source to include only those records where the Model column matches the selected value in the dropdown control named drpModel.
- Distinct Function:
- The Distinct function returns a one-column table that contains the unique values from the specified column of a data source.
- Syntax: Distinct(DataSource, ColumnName)
- ‘Fuel Type’:
- This specifies the column from which you want to extract unique values in the filtered data.
- The column name ‘Fuel Type’ should be a valid column in the ‘Cascading Data’ data source.
Conclusion
Cascading dropdowns in Power Apps provide a dynamic and interactive way to display related data, enhancing both user experience and data accuracy. By following the steps outlined above, you can implement this feature in your apps to create more intuitive and efficient forms and interfaces.
