Answered

Sorting a Gallery based off of a countRows function

(0) Share

Report

Posted on by Stikelee

I have a gallery that shows the count of my items in a sharepoint list, based off of a radio button selection. However, it is only able to sort based off of employee names. I want to have the highest counts at the top. Is there a way to get that data from the count rows pulled into a table that I can sort? I have been trying the AddColumns function but am not familiar with it.

Here is the Gallery Code -

Sort(GroupBy(Filter(AisleQualityCollection,StartsWith('Slot Number',Radio1.Selected.Title)),Title,Employee),Title,SortOrder.Ascending)

Here is the Count formula -

CountRows(Filter(AisleQualityCollection,Title=ThisItem.Title,Left('Slot Number',1)= Radio1.Selected.Title,'Reviewed?'="Yes"&Title<>"A1 -- Needs Researched"))

Thanks in advance!

Categories:

Building Power Apps

I have the same question (0)

All responses (14)

Answers (1)

Sort by

Verified answer

Ami K 15,687 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
@Stikelee - I think the below is what you're trying to do. Note that "Shaping" or "Transform" functions like AddColumns and GroupBy are not delegable with any data source.

Sort( AddColumns( GroupBy( Filter( AisleQualityCollection, StartsWith( 'Slot Number', Radio1.Selected.Title ) ), Title, GroupedItems ), CountItems, CountRows( Filter( GroupedItems, Left('Slot Number', 1)= Radio1.Selected.Title, 'Reviewed?'= "Yes", Title <> "A1 -- Needs Researched" ) ) ), CountItems, SortOrder.Descending )

Same result as above but easier to read:

With( { _prefiltered_data: Filter( AisleQualityCollection, StartsWith( 'Slot Number', Radio1.Selected.Title ) ) }, Sort( AddColumns( GroupBy( _prefiltered_data, Title, GroupedItems ), CountItems, CountRows( Filter( GroupedItems, Left('Slot Number', 1)= Radio1.Selected.Title, 'Reviewed?'= "Yes", Title <> "A1 -- Needs Researched" ) ) ), CountItems, SortOrder.Descending ) )

Was this reply helpful? Yes No
Stikelee 17 on at

Like (0)

Report
Copy link

Link copied!

@Amik I tried that in the items section of the gallery and it definitely changed the order.

This is what I am getting from the above:

Was this reply helpful? Yes No
Ami K 15,687 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@Stikelee - do you have a question?

Was this reply helpful? Yes No
Stikelee 17 on at

Like (0)

Report
Copy link

Link copied!

@Amik Yes, in the second image I show on this page, it has the counts but they are not in a descending or ascending order after using the code that you gave. How can I fix that?

Was this reply helpful? Yes No
Ami K 15,687 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@Stikelee - please share the code you have used (in text) and a screenshot of the output table. The screenshots you have shared do not tell me anything.

Was this reply helpful? Yes No
Stikelee 17 on at

Like (0)

Report
Copy link

Link copied!

@Amik sorry about the confusions. I tried the code that you gave above. Both instances gave me the same result. The code and output are shown below:

Sort(
AddColumns(
GroupBy(
Filter(
AisleQualityCollection,
StartsWith(
'Slot Number',
Radio1.Selected.Title
)
),
Title,
GroupedItems
),
CountItems,
CountRows(
Filter(
GroupedItems,
Left('Slot Number', 1)= Radio1.Selected.Title,
'Reviewed?'= "Yes",
Title <> "A1 -- Needs Researched"
)
)
),
CountItems,
SortOrder.Descending
)

Was this reply helpful? Yes No
Ami K 15,687 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@Stikelee - I want to look at the table returned from the formula.

On the Formula Bar, double click the "Sort" function at the top, and click "Table" (it might take a few seconds to appear), then share a screenshot of the output. Example below;

Was this reply helpful? Yes No
Stikelee 17 on at

Like (0)

Report
Copy link

Link copied!

It is returning this and the Title. The title has names so I did not want to share that.

Was this reply helpful? Yes No
Stikelee 17 on at

Like (0)

Report
Copy link

Link copied!

@Amik I also see it returning a lot of zeros towards the bottom of the count column. Not sure if that helps or not.

Was this reply helpful? Yes No
Ami K 15,687 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@Stikelee - the fact that the preview window is correctly sorting the count of items in descending / highest to lowest order, that should give you an indication that the formula is correct, but whatever you're doing in the Gallery is not...

Was this reply helpful? Yes No