web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Filter Gallery only to...
Power Apps
Unanswered

Filter Gallery only to show duplicate values

(0) ShareShare
ReportReport
Posted on by Lutzy 60

Hi Everyone,

I think this one might be a simple one but I can not seem to work it out.

I have an app with a Gallery that points to a SharePoint list, Asset database.

Let's say a user should only have 1 type of asset assigned to them for example a laptop or mobile phone. 

I would like to filter to only see records if a user has a duplicate Asset type of mobile phone with the status of assigned.

I have a dropdown at the top of the page where I will place the filter option.

I sort of have an understanding of what I need to do but will need some guidance,

Step 1 - Create new field in my gallery called Duplicate which will be a number field

Step 2 - Do a count rows and populate with a number on how many times a user has a particular asset in the Assigned Status.

Step 3 - On the gallery - If(Dropdown1.Selected.Value = "Users with Duplicate Laptops", Filter('Asset Database', 'Asset Type = "Laptops") && Duplicate.Value > 1))

I really need help with the second step

Thanks

Categories:
Building Power Apps
  • WarrenBelz Profile Picture
    WarrenBelz 156,526 Most Valuable Professional on at

    HI @Lutzy ,

    This should get you the result, but note it will only return a list of user names.

    Filter(
 AddColumns(
 GroupBy(
 Filter(
 'Asset Database', 
 'Asset Type = "Laptops" && 
 Status.Value = "Assigned"
 ),
 "YourUserFieldName"
 "Data"
 ),
 "Duplicates",
 CountRows(Data)
 ),
 Duplicates > 1
)

     

    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

  • Lutzy Profile Picture
    Lutzy 60 on at

    Thanks for the response @WarrenBelz 

     

    I think you have overestimated my ability.  Im fairly new to PowerApps.  Would you be able to explain your solution to me?

     

    I'm struggling with the add column part.  this will only work with a collection?

     

    I have my gallery pointing straight to the Sharepoint list. 

     

    Can you explain these lines - "YourUserFieldName" and "Data" and what these refer to?

     

    I have an app with a Gallery that points to a SharePoint list, Asset database.  The Gallery has a number of Columns.

    Lutzy_1-1666504729477.png

     

     

    Let's say a user should only have 1 type of asset assigned to them for example a laptop or mobile phone. 

    I would like to filter to only see records if a user has a duplicate Asset type of mobile phone with the status of assigned.

    I have a dropdown at the top of the page where I will place the filter option.

    Lutzy_2-1666504803695.png

    The other filters were easy in the drop-down are working really well with the following command.

    If(Dropdown1.Selected.Value = "Firewalls",Filter('Asset Database_2', 'Asset Type'.Value = "Firewall"),If(Dropdown1.Selected.Value = "Spare Mobiles",Filter('Asset Database_2', 'Asset Type'.Value = "Mobile Phone" && 'Assigned To'.DisplayName = "Spare Spare"),If(Dropdown1.Selected.Value = "All Assets",'Asset Database_2')))

     

    The aim is to filter by the ticket type of laptop and assigned and see if there are any duplicate usernames in the list.

     

    What is the best way to do this and is there a way I can do this through the same drop-down I have created? 

     

     

     

  • Drrickryp Profile Picture
    Drrickryp Super User 2024 Season 1 on at

    @Lutzy 

    Try this as the Items property for your gallery.

     

    With({ad:'Asset Database_2',
 atv:'Asset Type'.Value,
 laptp:Filter('Asset Database','Asset Type'.Value = "Laptops" && Status.Value="Assigned"),
 phone:Filter('Asset Database','Asset Type'.Value = "Mobile Phone"&& Status.Value="Assigned"),
 },
 Switch(Dropdown1.Selected.Value,
 "All Assets",'Asset Database_2',
 "Firewalls", Filter(ad, atv = "Firewall",
 "Spare Mobiles",Filter(ad, atv = "Mobile Phone" &&'Assigned To'.DisplayName = "Spare Spare",
 "Users with Duplicate Laptops",Filter(laptp,CountRows('Assigned To'.DisplayName) >1),
 "Users with Duplicate Mobiles",Filter(phone,CountRows('Assigned To'.DisplayName) >1)
)

     

     

  • WarrenBelz Profile Picture
    WarrenBelz 156,526 Most Valuable Professional on at

    @Lutzy ,

    You can condense your current filter to this

    Filter(
 'Asset Database_2', 
 Switch(
 Dropdown1.Selected.Value,
 "Firewalls",
 'Asset Type'.Value = "Firewall",
 "Spare Mobiles",
 'Asset Type'.Value = "Mobile Phone" && 
 'Assigned To'.DisplayName = "Spare Spare"
 "All Assets",
 true
 )
)

    however to eliminate duplicates, you need a GroupBy statement (as I posted)  and the output of this is the count of the grouped field (your user field) and the field name itself, so (as you requested), I provided what I believe is the code you need. You can "add back in" the other fields, but as these may contain many grouped records, you can only add in the first (or last) field in the record set. I believe the identifying of the duplicates needs to be a separate exercise to the Filter you now have as you cannot really combine them. However you can try the below to show the number of assigned laptops and mobile phones against each item. NOTE - this is free-typed and I cannot test it.

    With(
 {
 wLaptop:
 AddColumns(
 GroupBy(
 Filter(
 'Asset Database', 
 'Asset Type' = "Laptop" && 
 Status.Value = "Assigned"
 ),
 "Assigned To"
 "Data"
 ),
 "LaptopCount",
 CountRows(Data)
 ),
 wMobile:
 AddColumns(
 GroupBy(
 Filter(
 'Asset Database', 
 'Asset Type = "Mobile Phone" && 
 Status.Value = "Assigned"
 ),
 "Assigned To"
 "Data"
 ),
 "MobileCount",
 CountRows(Data)
 )
 },
 AddColumns(
 Filter(
 'Asset Database', 
 Dropdown1.Selected.Value = "All Assets" ||
 Switch(
 Dropdown1.Selected.Value,
 "Firewalls",
 'Asset Type'.Value = "Firewall",
 "Spare Mobiles",
 'Asset Type'.Value = "Mobile Phone" && 
 'Assigned To'.DisplayName = "Spare Spare"
 ),
 "LaptopCount",
 LookUp(
 wLaptop As aLaptop,
 aLaptop.'Assigned To' = 'Assigned To'
 ).LaptopCount,
 "MobileCount",
 LookUp(
 wMobile As aMobile,
 aMobile.'Assigned To' = 'Assigned To'
 ).MobileCount
 )
 )
)

     

     

    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

  • Lutzy Profile Picture
    Lutzy 60 on at

    Hi @WarrenBelz ,

    I really appreciate your help here.  I am trying to work with your first solution, break it down and work on each of the 3 components so I can learn the concepts a bit better.

    The filtering part is working as desired.  I am still trying to understand the Group by section. Can you explain what i should be using for the 'YourUserFeildName" ?  When I try and use the SP Field that I have called "Assigned To" it doesn't work and is not found. It is like it doesnt find the data in the Sharepoint list.

    I am not sure I understand the "Data" you have suggested also.

     

    AddColumns(

          GroupBy(

             Filter(

             'Asset Database',

                'Asset Type = "Laptops" &&

                Status.Value = "Assigned"

             ),

             "YourUserFieldName"

             "Data"

          ),

          "Duplicates",

          CountRows(Data)

       ),

       Duplicates > 1

    )

  • WarrenBelz Profile Picture
    WarrenBelz 156,526 Most Valuable Professional on at

    @Lutzy ,

    It should be Assigned To if that is the column name - it could also be Assigned_x0020_To. Have you renamed your Title field as this may be the original name you need to use. Data is the grouped column with the rest of the fields matching the grouped field. You can call it whatever you want.

  • Lutzy Profile Picture
    Lutzy 60 on at

    Thanks For your Suggestion @Drrickryp 

     

    I am giving this a try now.  Here is what I have 

    With({laptp:Filter('Asset Database_2',AssetType.Value = "Laptop" && Status.Value="Assigned")},
    Switch(Dropdown1_1.Selected.Value,
    "All Assets",'Asset Database_2',
    "Firewalls", Filter('Asset Database_2', AssetType.Value = "Firewall"),
    "Spare Mobiles",Filter('Asset Database_2', AssetType.Value = "Mobile Phone" && User.DisplayName = "Spare Spare"),
    "Users with Duplicate Laptops",Filter(laptp,CountRows(User.DisplayName) >1))

     

    I am getting an error message - Invalid Argument Type (Text). Expecting a Table value instead.  Any ideas?

  • WarrenBelz Profile Picture
    WarrenBelz 156,526 Most Valuable Professional on at

    @Lutzy ,

    It is because you cannot do this

    "Users with Duplicate Laptops",
Filter(
 laptp,
 CountRows(User.DisplayName) > 1
)

    and why I went to the effort of using GroupBy which is the only way to count matching records. You may actually get a valid filter with

    "Users with Duplicate Laptops",
Filter(
 laptp,
 CountRows(laptp.User.DisplayName) > 1
)

    but all that does is count all the rows in the Table - it does not match or group similar names.

     

  • Lutzy Profile Picture
    Lutzy 60 on at

    @WarrenBelz 

    You have been really helpful here and I appreciate it.

     

    I created a whole new column called "User"  to test this out and eliminate any issues elsewhere.  

     

    I hope this image below helps

     

    This is what I have - 

    Lutzy_0-1666727859419.png

     

     

     

  • WarrenBelz Profile Picture
    WarrenBelz 156,526 Most Valuable Professional on at

    @Lutzy ,

    That is one part of it, but only returns two columns - User_Column1 and Data (which is a Table). Also throw away that Data Table and use a Gallery and have a look at the full code I posted.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the July Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
11manish Profile Picture

11manish 402 Super User 2026 Season 2

#2
Mohsin Ali Profile Picture

Mohsin Ali 328

#3
WarrenBelz Profile Picture

WarrenBelz 296 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans