cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
luisep
Helper II
Helper II

Multiple columns in gallery - Distinct filter

Hi everyone,

I'd like to know how I could show multiple columns in gallery if a filter by Distinct.

 

My scenario:

SPlist1:

Cod EntityNumberCodeDescription
A1C1Description Code C1
A2N85Description Code N85
A3FB2Description Code FB2
B1C1Description Code C1
B2N85Description Code N85
B3FB2Description Code FB2

 

Gallery1:

Items: Distinct(SPList1,Number)

 

My problem: the gallery shows the column 'Number' as a result of Distinct, but I also want to add the columns 'Code' and 'Description'.

Gallery would be as this:

 

NumberCodeDescription
1C1Description Code C1
2N85Description Code N85
3FB2Description Code FB2

 

Thanks for your help.

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@luisep 

Consider using the GroupBy function instead of Distinct for this.  Distinct will give you a table with the Distinct values of the column you specify...and nothing else!

GroupBy will allow you to group them as you need.

 

So, this for your Items property formula:

   GroupBy(yourDataSource, "Number", "Code", "Description", "_records")

 

Will produce the results you want.

The _records column will have all the records that match that group (at least one).

 

I hope this is helpful for you.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

7 REPLIES 7
RandyHayes
Super User
Super User

@luisep 

Consider using the GroupBy function instead of Distinct for this.  Distinct will give you a table with the Distinct values of the column you specify...and nothing else!

GroupBy will allow you to group them as you need.

 

So, this for your Items property formula:

   GroupBy(yourDataSource, "Number", "Code", "Description", "_records")

 

Will produce the results you want.

The _records column will have all the records that match that group (at least one).

 

I hope this is helpful for you.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
GPronnya
Helper II
Helper II

@RandyHayes Hello, thanks for your reply. Can you please advice on how to perform COUNT of such distinct rows?

 

and also how in gallery I can make additional labels to show values of other colums such as Record, Description.

 

Thanks in advance.

@RandyHayes That was the solution that i was looking for!, Thank you!!!!

jjacquet
Frequent Visitor

This solution works well for small datasets & this question. GroupBy() is not a delegable function, so if your dataset contains >500 records (or >2000 if you increase limit) you will likely get incomplete results if you're trying to apply this solution to something larger.

 

I came across this while looking to do something similar against a SQL Server table with >10,000 records. This requires pre-filtering your dataset before executing the GroupBy().

 

https://docs.microsoft.com/en-us/power-apps/maker/canvas-apps/delegation-overview#non-delegable-func...

 

 

RandyHayes
Super User
Super User

@jjacquet 

Delegation and Record Limit are two different things.  

No function in PowerApps will ever return more than the record limit of the app.

 

Criteria used in filters, lookups, etc. is what is subject to delegation.  It is either delegable to the datasource or not.

However, that function will still never return more than the record limit.

 

GroupBy, AddColumns, ShowColumns, RemoveColumns, Distinct, Index, Shuffle, Sort, SortByColumns, Ungroup, etc. are all functions that never delegate (because there is no criteria to delegate), so they are always restricted to the same record limits as all the other functions.

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
jjacquet
Frequent Visitor

@RandyHayes - of course, you're right & I realized my mistake shortly after hitting 'reply'. I apologize.

 

The point does remain that there's a limit to using GroupBy, but the rationale was wrong.

 

Appreciate your contributions to this forum!

RandyHayes
Super User
Super User

@jjacquet 

No worries.  And it is an important thing to know...those functions do not delegate because there is nothing to delegate, so, they do what any other function would do...only return <record max> records.

 

It is very common for people to try and work around delegation errors by using things like AddColumns to "shape" their data into something they can use to avoid delegation...but it is a futile effort because you are still only going to deal with the max record limit.

 

Always happy to contribute!! 😁

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (5,029)