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 / CountRows with Filter ...
Power Apps
Answered

CountRows with Filter and in function

(0) ShareShare
ReportReport
Posted on by akbarmahfuzalam 709

Hey Team,

 

I am stuck with one of the formulas wherein I am trying to get the countRows with Filter and use in the function.

I have one master list for Employee and I have one Sharepoint list so what I am trying to build here is with SharePoint I have one field called Team Lead and another field called status.
Check the Employee list and see who is the manager of the Team Lead who has uploaded the data in SP List. Need Manager wise count of pending approval.

 

Employee List

employeeList.JPG

SP List

IDEnterprise_IDLOBQueueReasonTeam Lead
65abczxyaxcBlah BlahMr X
66   Blah BlahAmir D
67   Blah BlahAkbar

 

CountRows(Filter(HBAdherenceLabelling,RequestedBy in (Filter(WHOSWHO,TeamLead=ddTeam_22.Selected.Result,TeamLead).TeamLead) And Status.Value= "Pending Approval"))

 

 

PowerApps.JPG

Categories:
Building Power Apps
I have the same question (0)
  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    Try adding another filter instead of using And.

    CountRows(Filter(Filter(HBAdherenceLabelling, Status.Value = "Pending Approval") ,RequestedBy in (Filter(WHOSWHO,TeamLead=ddTeam_22.Selected.Result,TeamLead).TeamLead)))
  • akbarmahfuzalam Profile Picture
    akbarmahfuzalam 709 on at

    Thanks or your Prompt response.

    It's not giving me any result

    akbarmahfuzalam_0-1597414913033.png

     

    CountRows(Filter(Filter(HBAdherenceLabelling, Status.Value = "Pending Approval") ,RequestedBy in (Filter(WHOSWHO,TeamLead=ddTeam_22.Selected.Result,TeamLead).TeamLead)))

     

    Do i need to tweet somewhere in the second filter by changing it to the Manager field?

     

  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    You might need the dropdown field name instead of ddTeam_22.Selected.Result so it could be ddTeam_22.Selected.TeamLead.

     

    CountRows(Filter(Filter(HBAdherenceLabelling, Status.Value = "Pending Approval") ,RequestedBy in (Filter(WHOSWHO,TeamLead=ddTeam_22.Selected.TeamLead).TeamLead)))

     

     

  • akbarmahfuzalam Profile Picture
    akbarmahfuzalam 709 on at

    My Field name is ddTeam_22

     

    akbarmahfuzalam_0-1597417153315.png

     

  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    Is this a gallery? If so you should use ThisItem.TeamLead instead of ddTeam_22.Selected.TeamLead.

  • akbarmahfuzalam Profile Picture
    akbarmahfuzalam 709 on at

    @Anonymous 

     

    It is not given me the result even though I used the above-mentioned formula

     

    CountRows(Filter(Filter(HBAdherenceLabelling, Status.Value = "Pending Approval") ,RequestedBy in (Filter(WHOSWHO,TeamLead=ThisItem.Result).TeamLead)))

     

    akbarmahfuzalam_0-1597427720160.png

     

  • RandyHayes Profile Picture
    RandyHayes 76,299 Super User 2024 Season 1 on at

    @akbarmahfuzalam 

    I'm not entirely sure how your logic is flowing here.  It seems that you have a dropdown with a list of TeamLeads in it, then you are trying to Filter the WHOSWHO list to get the TeamLead out of the values that match the TeamLead selected.  Aren't they one and the same?  And since you want this from the WHOSWHO list, It seems your formula should just be this:

    CountRows(
 Filter(WHOSWHO,
 RequestedBy = ddTeam_22.Selected.Result
 && Status.Value= "Pending Approval"
 )
)

    But your definition of the logic seems somewhat different. 

     

    And I've read your logic here and in your message saw a little different way as well of what you are doing.

    I read it as perhaps WHOSWHO is your employee list that you mention and that HBAdherenceLabelling is the list you want a count from.

    If you are listing the WHOSWHO in your SummaryHB Gallery and then want to get a count of the HBAdhearenceLabelling for the TeamLead, then consider this formula:

    CountRows(
 Filter(HBAdhearanceLabelling, 
 RequestedBy = ddTEam_22.Selected.Result
 && Status.Value = "Pending Approval"
 )
)

     

    But then again, I also saw your pictures that showed ShiftLead in the WHOSWHO list.  Is that what you want to get or the TeamLead of what?

     

    See if I am getting close to what you are looking for, if not.  Perhaps be clear on the list names and some sample data and expected results.  Please mock up the data to not release names.  But provide some correlating data.  In your original post you have Team Leads that look mocked up, but then the picture of the App has only one name shown which doesn't correlate to anything in the other lists.

  • akbarmahfuzalam Profile Picture
    akbarmahfuzalam 709 on at

    @RandyHayes 

     

    Please accept my apology for replying to you back Late.

     

    Basically, there are SP list and need report kind of a summary wherein I want shiftlead wise Count whose Team lead is using the App and uploading the Data.

    And I have WHOSWHO SP List where TeamLead and their ShiftLead are mentioned.

     

    Have attached the Report and SP List for your Kind Reference.

     

    FYI... WHOSWHO Field TEAM is equal to LOB of HBAdherenceLabelling

    WHOSWHO.JPG
    HBAherenceReportview.JPG
    SPHB.JPG
  • RandyHayes Profile Picture
    RandyHayes 76,299 Super User 2024 Season 1 on at

    @akbarmahfuzalam 

    I am still trying to digest completely what you are trying to really get at.  I believe what you are trying to do is:

     

    1) Dropdown with list of Teams from the WHOSWHO list (ddTeam_22).  If I was guessing, I would say your Formula for the Dropdown Items property is :  WHOSWHO  and you are displaying the Team column. (However I don't believe this is your formula since you probably have a distinct list there).

     

    2) Your Gallery is a list of the People (FullName column perhaps) that are in that Team selected.  Expecting Items property to be something like:  Filter(WHOSWHO, Team=ddTeam_22.Selected.Team)

     

    3) You have a Label in your Gallery Template that you wish to have display the number of "Pending Approval" rows that are in the HBAdherenceLabelling list where the RequestedBy is equal to the Team Lead of the Team that is selected in the DropDown

     

    HOWEVER, you added something in your last message that makes me scratch my head a little - you said that the TEAM column in the WHOWSWHO list is equal to the LOB in the HBAdherenceLabelling list.

     

    So if that is the case, then I believe it is not so much about the RequestedBy that you want to count rows as much as the LOB for the Team that is selected in the DropDown.

     

    I am really not quite getting exactly what you want because I do not see a correlation in the data to the display - there is too much that is crossed out in your screen shots. And what is visible, NONE of it matches anything in any other list. 

    I really need to see this data and how it correlates to what you are trying to do.  As mentioned before, you can easily mock up some data to try and convey what you need.

     

    Like this:

    WHOSWHO list (only needed columns shown)

    Team FullName TeamLead
    TeamA John Doe Leader1
    TeamA Mary Jane Leader1
    TeamB Bill Gates Leader2
    TeamC Donald Duck Leader3

     

    HBAdherenceLabelling list (only needed columns shown)

    LOB RequestedBy Status
    TeamA Leader1 Approved
    TeamB Leader2 Pending Approval
    TeamC Leader3 Pending Approval
    TeamA Leader1 Pending Approval

     

    And for your App Screen

    Dropdown is a list of What?  (what is the formula on the Items?)

    Gallery is a list of What? (what is the formula on the Items?)

     

    ONCE we can get the above clear, I believe we can get your count correct.  But, I keep guessing at how this all connects and I just need to see mocked up data like above to be able to say.  If TeamA is chosen, then we should see "X" in the Gallery and a count of "Y" in the label.

     

    Please fill in some of the details and we'll certainly get this nailed down.

  • akbarmahfuzalam Profile Picture
    akbarmahfuzalam 709 on at

    @RandyHayes 

     

    Thanks for dip analysis for my problem.

     

    Here what I wanted.

     

    WHOSWHO    in SharePoint List
    TeamFullNameTeamLeadShiftLead
    TeamAJohn DoeLeader1ShiftLead1
    TeamAMary JaneLeader1ShiftLead1
    TeamBBill GatesLeader2ShiftLead1
    TeamCDonald DuckLeader3ShiftLead3
    TeamAApril DonnamLeader3ShiftLead3
    TeamCCharles DickmanLeader1ShiftLead1
    TeamDLuis GrantLeader4ShiftLead2

     

    HBAdherenceLabelling      in SharePoint List
    LOBRequestedByStatus
    TeamALeader1Approved
    TeamBLeader2Pending Approval
    TeamCLeader3Pending Approval
    TeamALeader1Pending Approval
    TeamALeader3Approved
    TeamDLeader2Pending Approval
    TeamCLeader2Pending Approval

     

    Need
    ShiftLeadApprovedPending Approval
    ShiftLead113
    ShiftLead2 1
    ShiftLead311

     

    As of now What i did . I inserted a Gallery named it as SummaryHB and I inserted a formula

     

     

    Distinct(WHOSWHO,ShiftLead)

     

     

    Summary.JPG

    Summary2.JPG


    Wherein I am able to get all The ShiftLead Name Now I want to see what is the count in Number Pending Approval and Approved for their TeamLead.

     

     

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 Launch!

Jump in, show your community spirit, and win prizes!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the May Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
Valantis Profile Picture

Valantis 424

#2
WarrenBelz Profile Picture

WarrenBelz 355 Most Valuable Professional

#3
11manish Profile Picture

11manish 290

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