cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Al12397
Regular Visitor

Help with filtering combo Box in Forms

So I have this Data set lets call it DIM_Retail_Table and the data looks similar to the following

IDRegionStoreDepartmentProducts
1EastWalmartFresh GreensBroccoli
2EastWalmartElectronicHP Laptop
3EastTargetHealthToothbrush
4CentralTargetElectronicPS5
5CentralTargetFurnitureSofa
6CentralCVSHealthToothbrush
7WestCVSHealthSoap
8WestTargetElectronicPS5
9WestWalmartFresh GreensBroccoli


Through Power Apps users will have 4 combo boxes with items being Region, Store, Department, Items from the "DIM_Retail_Table" above. They will also have a text inputs where they can input Store location (Cities) and Price for item. The resulting Fact table ("Fact_retail_Table") should look like this.


IDRegionStoreDepartmentProductsStore LocationPriceCreator 
2EastWalmartElectornicHP LaptopDallas495John Doe
2EastWalmartElectornicHP LaptopHouston525Sam Wilson
5CentralTargetFurnitureSofaMiami1500Jane Doe
7WestCVSHealthSoapDallas2.95John Doe
4CentralTargetElectornicPS5New York550Aubrey Tart
5CentralTargetFurnitureSofaNew York1450Ailen James


So I have created a form which updates the Fields in the Fact table. I have made custom cards so that those cards take values from the DIM table above. Users should ideally select a region which filters Store which then will filter Department and then finally Items. All these items should be the unique of the column. For example in the first combo box for region only the unique (east, West, Central) values should show up)

My problem

- I have got this functionality sort of working by doing something like this (this is the items property of the Product card)

  •     - Distinct(
    Filter(
    DIM_Retail_Table_,
    Region = First(cb_Region_question.SelectedItems).Result && Store = First(cb_Topic_question.SelectedItems).Result && Department) = First(cb_QuestionCategory_Questions.SelectedItems).Result
    ),
    Products
    )
  • Doing it this way has limitations for example what if I want to see all the products in the East region without putting in values for the "Store and Department" combo boxes. Right now those would need to be populated in order to see the listing
  • Also with this way once the users makes selections all the way to product what happens if they change regions. How can I make it so the combo boxes dont show regions not associated with a particular product
  • Pretty much how can I make this function like power BI Slicers to assure data integrity 


 






 

1 ACCEPTED SOLUTION

Accepted Solutions
StalinPonnusamy
Community Champion
Community Champion

Hi @Al12397 

 

Set OnChange property of the Dropdown1 to

Reset(DropdownName2)

 

Example

StalinPonnusamy_0-1632842240388.png

 

View solution in original post

5 REPLIES 5
StalinPonnusamy
Community Champion
Community Champion

Hi @Al12397 

  1. You have to use the Dependent dropdown concept. I recommend referring to MS Article
  2. Need to reset Store control when region changes. Like wise other controls as well
StalinPonnusamy
Community Champion
Community Champion

Hi @Al12397 

 

I hope you have gone through the MS article about the dependent dropdown concepts. Please let us know if anything needs on your post. We can help with this.


Please do not forget to give kudos if you find the suggestion helpful or Accept it as a solution if works fine to help other users to find it useful.

How Do I reset a control. What would that code look like?

StalinPonnusamy
Community Champion
Community Champion

Hi @Al12397 

 

Set OnChange property of the Dropdown1 to

Reset(DropdownName2)

 

Example

StalinPonnusamy_0-1632842240388.png

 

View solution in original post

Nice! didnt know about the OnChange Property!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,217)