cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
neill_long
Continued Contributor
Continued Contributor

Filter and Distinct

Hi

 

I have a gallery which has the following formula:

 

Filter(
    LDR_UniversitiesTEST,Dropdown2_1.Selected.Value = Blank() And Department = CoursesList.Dept && Removed = "No" && IsBlank(SearchBox_2.Text) Or Session = Dropdown2_1.Selected.Value And Department = CoursesList.Dept && Removed = "No" && IsBlank(SearchBox_2.Text) || Department = CoursesList.Dept && Removed = "No" && StartsWith(
        Course,
        SearchBox_2.Text
    ) && Session = Dropdown2_1.Selected.Value And Department = CoursesList.Dept && Removed = "No" Or Department = CoursesList.Dept && Removed = "No" && StartsWith(
        Course,
        SearchBox_2.Text
    ) && Dropdown2_1.Selected.Value = Blank() And Department = CoursesList.Dept && Removed = "No")

 

This gallery show items where the department is equal to the selected record stored in a variable.  but I also need to do a distinct on it to remove the duplicate courses.

 

I added Distinct at the start and then at the end I added ,Course):

 

Distinct(Filter(
    LDR_UniversitiesTEST,Dropdown2_1.Selected.Value = Blank() And Department = CoursesList.Dept && Removed = "No" && IsBlank(SearchBox_2.Text) Or Session = Dropdown2_1.Selected.Value And Department = CoursesList.Dept && Removed = "No" && IsBlank(SearchBox_2.Text) || Department = CoursesList.Dept && Removed = "No" && StartsWith(
        Course,
        SearchBox_2.Text
    ) && Session = Dropdown2_1.Selected.Value And Department = CoursesList.Dept && Removed = "No" Or Department = CoursesList.Dept && Removed = "No" && StartsWith(
        Course,
        SearchBox_2.Text
    ) && Dropdown2_1.Selected.Value = Blank() And Department = CoursesList.Dept && Removed = "No"),Course)

 

But the fields in the gallery are now showing error.  e.g. label that had ThisItem.Course in its default.  It will only accept ThisItem.Result which shows the department name.

 

Is it possible for it to show the relevant fields instead of just the 'Result' of the distinct?

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @neill_long,

 

As you say, the Distinct function returns a one-column table of the results with duplicate values removed. The name of the column is Result.

 

The GroupBy function should help as you can return multiple columns using this format:

GroupBy( Table, ColumnName1 [, ColumnName2, ... ], GroupColumnName )

 

GroupBy function reference: https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-groupby


If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

11 REPLIES 11
neill_long
Continued Contributor
Continued Contributor

If distinct is not the answer then maybe a GroupBy?  but can someone help?

 

Hi @neill_long,

 

As you say, the Distinct function returns a one-column table of the results with duplicate values removed. The name of the column is Result.

 

The GroupBy function should help as you can return multiple columns using this format:

GroupBy( Table, ColumnName1 [, ColumnName2, ... ], GroupColumnName )

 

GroupBy function reference: https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-groupby


If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

cha_cha
Resident Rockstar
Resident Rockstar

Hello @neill_long 

 

this is just how I usually do to when my code gets long but maybe someone can give you a better answer. 

 

(1) Duplicate your Gallery.

(2) Original Gallery will be Gallery A and Hide It.

(3) New Copied Galler will be Gallery B and you add the code below to its Item property.

 

 

With({_items: Distinct(GalleryA.AllItems,Course), _details:GalleryA.AllItems},
    ForAll(_items As A,
        Patch(LookUp(_details,Course=A.Result),{})
        ) 
    )

 

 

Gallery B would just be a duplicate of Gallery A.

cha_cha_0-1669210047521.png

 


 
Just in case you my answer helped you solve your problem, please mark/accept this as a SOLUTION This helps community members if they experience a similar issue in the future.

 
bistek.space   @cha_bistek    @BisTekSpace 
neill_long
Continued Contributor
Continued Contributor

@cha_cha

 

When I enter your formula on the gallery, it should display 4 departments, but it only shows 2.  It will only show 4 when I scroll down on Gallery A to display records that have the remaining departments.  Is there something i need to add to ensure all 4 departments will show?

 

thanks

cha_cha
Resident Rockstar
Resident Rockstar

So does it mean some courses may have multiple departments?


 
Just in case you my answer helped you solve your problem, please mark/accept this as a SOLUTION This helps community members if they experience a similar issue in the future.

 
bistek.space   @cha_bistek    @BisTekSpace 
neill_long
Continued Contributor
Continued Contributor

each course will have 1 department.  there are only 4 departments but there are multiple courses which will be connected to a department.

cha_cha
Resident Rockstar
Resident Rockstar

Sorry, can you please show me a sample result you want and what is currently showing? I'm bad at visualizing words.


 
Just in case you my answer helped you solve your problem, please mark/accept this as a SOLUTION This helps community members if they experience a similar issue in the future.

 
bistek.space   @cha_bistek    @BisTekSpace 
neill_long
Continued Contributor
Continued Contributor

So this is what is currently showing: Gallery B:

neill_long_0-1669217651133.png

On the 'Items' of Gallery B I have the following to show the Department Names:

 

With({_items: Distinct(Gallery3.AllItems,Department), _details:Gallery3.AllItems},
    ForAll(_items As A,
        Patch(LookUp(_details,Department=A.Result),{})
        ) 
    )

 

The hidden gallery, Gallery A, lists all the records, I have it showing the course names and the department:

 

neill_long_2-1669217891421.png

 

This is what should be displaying:

 

neill_long_1-1669217698111.png

 

 

cha_cha
Resident Rockstar
Resident Rockstar

Hi @neill_long 

With({_items: Distinct(GalleryA.AllItems,Course & "##" & Department), _details:GalleryA.AllItems},
    ForAll(_items As A,
        Patch(
            LookUp(_details
            ,Course=First(Split(A.Result,"##")).Result && Department=Last(Split(A.Result,"##")).Result
            ),{})
        ) 
    )

 


 
Just in case you my answer helped you solve your problem, please mark/accept this as a SOLUTION This helps community members if they experience a similar issue in the future.

 
bistek.space   @cha_bistek    @BisTekSpace 

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 (2,105)