Answered

Filtering a Data Table Based on Selections from ComboBox

(0) Share

Report

Posted on by Applications

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:

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!

Categories:

Building Power Apps

I have the same question (0)

All responses (26)

Answers (1)

CNT 10,921 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Applications on at

Like (0)

Report
Copy link

Link copied!

All I have in Items property for the DataTable is:
'Table'

Was this reply helpful? Yes No
v-bofeng-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
Applications on at

Like (0)

Report
Copy link

Link copied!

So I put this code in the Items for the Table and it shows as this:
When filling in the comboboxes with selections the table shows as this as well:

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

Was this reply helpful? Yes No
v-bofeng-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
Applications on at

Like (0)

Report
Copy link

Link copied!

Now I get these errors:

And this one:

Was this reply helpful? Yes No
Applications on at

Like (0)

Report
Copy link

Link copied!

Anyone able to help with this by chance?

Was this reply helpful? Yes No
CNT 10,921 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Applications on at

Like (0)

Report
Copy link

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

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!

Was this reply helpful? Yes No
WarrenBelz 154,496 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @Applications ,

You need to put the Equipment back in the columns

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

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

Was this reply helpful? Yes No