I came across a requirement to have multiple optional filters in the data table. I know we have done a lot of filters in the Gallery or Data table in PowerApps but this one was a bit challenging.
Optional Filter View
Flow
As we know flow chart always helps to understand better and easier. Here are the keep concepts for this logic
- Loop through each selection to filter data
- Once the first filter is applied, then use filtered data as a source instead of the main data source
- Set the flag whether the filter is applied or not, so that we can define the data source to assign to the data table
Data Table Items = If(FilteredApplied, ColFilteredData, DimCustomer)
Code Explanation
Screen OnVisible Property
- Create a simple collection that has numbers (Table) that are used to do the iteration. This also defines the max number of filters per control
-
//Simple table – Will be used to get specific value from dropdown
ClearCollect(IterationTable,[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25]);Code for the Filter button event
Filter button OnSelect Property
- Initialize and collect data from source
- Get each filter count (Combobox Selectection Count)
- Filter 1 – Martial Status
- Define which one is a data source for the next filter
- Filter 2 – Gender
- Filter 3 – Same steps as a filter 2 (Goes until N filters)
Fx Code for Filter Command
Demo
NOTE: My data source is an On-premises SQL server and retrieving more than 2000 by combining multiple requests with concurrent.
Refer my other blog which explains to connect on-premises data
Hi, I have followed this to a T & cannot get the filter to function correctly. In this bit of code here, are you referencing a table column or the data source in the equals section?
If(
BusinessUnitStatusCount > 0,
UpdateContext({FilteredApplied: true});
ForAll(
SpecificIteration,
Collect(
ColFilteredData,
Filter(
ColMainData,
‘Follow-Up Priority_Column2’.Text = Last(
FirstN(
PriorityCombobox.SelectedItems,
SpecificIteration[@Value]
)
).Value
)
)
)
);