Counting the number of occurrences of an item from a SharePoint list and sorting another list based on the highest number of occurrences

[rajeshcs]

Hello Everybody

I am new to PowerApps and learning from the videos many of you have posted in Youtube. This community page is also helping in my learning journey. I am developing a canvas application and hit with an issue.

I have two SharePoint lists. AssetList which stores the details of various assets. Second list (LikesList) which stores the number of Likes for the asset items. Whenever somebody clicks the like button the AssetID and the email of the user is is stored in the LikesList. The LikesList will look like this

AssetID	eMail
1	user1@abc.com
1	user2@abc.com
3	user1@abc.com
2	user2@abc.com
1	user5@abc.com
3	user4@abc.com
3	user2@abc.com

I want to count the number of likes for each asset and display the assets which is having highest number of likes  on top in a gallery.

I have created a collection with Distinct function to collect the AssetsIDs which have been liked by the users from the LikesList. ClearCollect(likes, Distinct(LikesList,AssetID) which is bringing back the individual AssetIDs which people have liked. I have then created a gallery with the collection likes and added a label to count the number of likes against each asset ID.

AssetID1	LikesCount
1	3
2	1
3	3

Now I want to filter  the AssetList gallery with the AssetID1 column and sort it with the LikesCount from Gallery2. I tried AddColumn to create a collection combining the AssetList and Gallery2 with no success.

 Is there a way I can achieve this? Is there a better solution for this scenario? Any help on this will be greatly appreciated.

Thanks in Advance

Rajesh

[RandyHayes]

@rajeshcs 

You will need to utilize the Group by function for this.

Set your Gallery Items Property to:

SortByColumns(
    AddColumns(
        GroupBy(yourDataSource, 
            "AssetID", 
            "_recs"
        ), 
        "Likes", CountRows(_recs)
    ), 
    "Likes", Descending
)

I hope this is helpful for you. 

[rajeshcs]

Thanks @RandyHayes! The solution worked perfectly fine for counting the Likes and sorting in descending order. Now I want to filter my actual asset gallery when ID matches the AssetID from the previous formula. How do i write the filter function for the same? Apologies for my poor knowledge in the subject.

[RandyHayes]

@rajeshcs 

No problem, but I am not understanding what you mean.  The formula provided gives you the Likes in top to bottom order. So, it is a table.  I am lost when you say filter the gallery (what gallery?) when ID matches (where is the ID?) the AssetID from the previous formula (the formula provides a table...there are many AssetID's...which do you want, the top one?)

[rajeshcs]

@RandyHayes 

Thanks for helping me.

Sorry for the confusion. From my original post, I have two SharePoint Lists
AssetList
ID Asset Name Asset Owner Created Asset Type
1
2
3
4
13
This AssetList is connected to a Galley which displays the assets. I have a like button in the assets galley associated with each asset and when people clicks on the like button it writes data to the LikesList. The LikesList is as follows.
AssetID   eMail
1      user1@abc.com
2      user3@abc.com
13    user1@abc.com
13    user4@abc.com
3     user2@abc.com
2     user3@abc.com
13     user2@abc.com
Here the AssetID corresponds to the ID in my first list (AssetList)
I want to actually display the assets in my asset galley where the assets with maximum number of likes appears first. Hope this clarifies.
Thanks
Rajesh

[RandyHayes]

@rajeshcs 

Sorry, I am not understanding...that was the formula that I provided for you.  It displays the assets in the gallery with the maximum number of likes appearing first.

[rajeshcs]

@RandyHayes  Apologies for my poor understanding and I am sure, I may be using the formula wrongly 😥

Here is my issue. Apologies if I am asking a silly question again and again. 

Thanks

Rajesh

[RandyHayes · Accepted solution]

@rajeshcs 

Yes, everything I provided was based off of your original post where you had an AssetID.

Your AssetGallery should have the formula that I provided in it with the change that, if you want the asset information, the look that up:

SortByColumns(
    AddColumns(
        GroupBy(yourDataSource, 
            "AssetID", 
            "_recs"
        ), 
        "Likes", CountRows(_recs),
        "Asset", LookUp(AssetList, ID=AssetID)
    ), 
    "Likes", Descending
)

You will now have the full Asset record in your table records that you can use.

ex. ThisItem.Asset.'Asset Name'

[rajeshcs]

Thanks a ton @RandyHayes 

It worked exactly like I wanted it. Thank you so much for helping me. It was very good learning experience for me.

Thanks

Rajesh