cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Applications
Helper III
Helper III

Filtering a Data Table Based on Selections from ComboBox

Hello! I'm trying to make a data table show filtered, distinct records when selections are made in a combo box, please see below for example:

 

Applications_0-1639065878391.png

 

When a user selects equipment, remaining shows the inventory for that piece of equipment within the building in the first combobox.

The purpose of the data table will be to show the user that piece of inventory in places OTHER than the building being selected. So what I need it to do is filter out the non-redacted items from the building column, I got the equipment column to be what I wanted, but also the Remaining Column needs to only show values > 0. I also need it to be a distinct or grouped list so that the two blue-redacted items show only as one, the black redacted items will show as 1 as well, and it will sum the total items for those grouped in the remaining column.

 

Further insight for the PowerApps:

ComboBox1_1 = Distinct('Table', Building)

ComboBox1 = Distinct(Filter('Table', Building=ComboBox1_1.Selected.Result), Equipment)

Label = Sum(Filter('Table', Building=ComboBox1_1.Selected.Result && Equipment=ComboBox1.Selected.Result), Remaining)

Equipment Column in Data Table = ComboBox1.Selected.Result

 

Is this even possible? If so, please help me navigate through this - I've been trying to get it set up correctly to no avail. Thank you all!

1 ACCEPTED SOLUTION

Accepted Solutions

@Applications ,

After all of this, I see the issue - you will note I have mentioned Gallery several times - you have a Data Table. These do not behave like a Gallery, particularly when you modify the Items beyond a simple filter. Consider using a Gallery and this all should work.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

View solution in original post

26 REPLIES 26
CNT
Super User
Super User

@Applications What do you have in the Items property of the DataTable?

All I have in Items property for the DataTable is:

'Table'

v-bofeng-msft
Community Support
Community Support

Hi @Applications :

Do you want to get a summary table of selected equipment in other buildings?If so, please try this formula(Because the formula is free to enter, if there is an error, please modify it according to the prompt):

ShowColumns(
 AddColumns(
   GroupBy(
      Filter('Table', Building <> ComboBox1_1.Selected.Result && Equipment=ComboBox1.Selected.Result),
      "Building"
      "NewGroup"
   ),
  "SUM",
 Sum(NewGroup,Remaining)
 ),
 "Building",
 "Equipment",
 "SUM"
)

Best Regards,

Bof

So I put this code in the Items for the Table and it shows as this:

Applications_0-1639406621533.png

When filling in the comboboxes with selections the table shows as this as well:

Applications_1-1639406715117.png

 

Let me know if there's anything else I need to do on my end! Thank you so much!

 

Hi @Applications :

Please try:

ShowColumns(
 AddColumns(
   GroupBy(
      Filter('Table', Building <> ComboBox1_1.Selected.Result && Equipment=ComboBox1.Selected.Result),
      "Building",
      "NewGroup"
   ),
  "SUM",
 Sum(NewGroup,Remaining)
 ),
 "Building",
 "Equipment",
 "SUM"
)

Best Regards,

Bof

Now I get these errors:

Applications_0-1639488588130.png

 

And this one:

Applications_1-1639488623391.png

 

 

Applications
Helper III
Helper III

Anyone able to help with this by chance?

@Applications Can you paste your existing formula in the Chat.

Absolutely! Here are all of the formulas I have so far:

 

Applications_0-1640096792529.png

 

For Building: 

Distinct('ITID Equipment Tracker v2', Building)

For Equipment: 

Distinct(Filter('ITID Equipment Tracker v2', Building=ComboBox1_1.Selected.Result), Equipment)

For Remaining: 

Sum(
Filter(
'ITID Equipment Tracker v2',
Building=ComboBox1_1.Selected.Result &&
Equipment=ComboBox1.Selected.Result)
, Remaining)

For the table:

Items:

ShowColumns(
AddColumns(
GroupBy(
Filter('ITID Equipment Tracker v2', Building <> ComboBox1_1.Selected.Result && Equipment=ComboBox1.Selected.Result),
"Building",
"NewGroup"
),
"SUM",
Sum(NewGroup,Remaining)
),
"Building",
"Equipment",
"SUM"
)

 Text in the building column of the table:

ThisItem.Building

 Text in the equipment column of the table:

ThisItem.Equipment

Text in the remaining column of the table:

ThisItem.Remaining

 

Again, the goal I'm looking for is to get the remaining of equipment by building, but the building needs to be distinct and the remaining needs to be summed for the building since the dataset has the same building numerous times. 

 

Thank you so much!

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (3,746)