cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
StuPower
Helper I
Helper I

How to count the number of Office365 users

I would like to be able to count the number of users that share the same senior manager, this query will display this person - Office365Users.Manager(Office365Users.Manager(Office365Users.MyProfile().Mail).Mail).DisplayName The requirement is that when the senior manager logs in to my app a label will show the amount of users they are responsible for I have this to do a count but i don't know how to combine the two CountA(Office365Users.SearchUser({searchTerm:""}).Mail) Is this possible? Thanks
1 ACCEPTED SOLUTION

Accepted Solutions

@StuPower 

Is it only two levels down (user / reports / reports)?  If so, then this formula might be more helpful for you:

 

Clear(directs);
ForAll(Office365Users.DirectReportsV2(User().Email).value, 
       Collect(directs, 
           {user:mail, 
            reports:ShowColumns(Office365Users.DirectReportsV2(mail).value, "mail")}
                 )
           );
ClearCollect(directReports, 
            DropColumns(
                  Ungroup(directs, "reports"), 
                  "user")
            )

After this formula is used, your directReports collection should have the list of all the emails of the people that are direct reports and their direct reports as well.

CountRows(directReports) will be the number of reports and their reports.

 

I hope this is more 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

9 REPLIES 9
RandyHayes
Super User
Super User

@StuPower 

Have you considered using the DirectReports function to get the direct list of people the person manages rather than getting all the users and seeing who has that manager?

A formula such as this would give you the count:

CountRows(Office365Users.DirectReportsV2(User().Email).value)

A formula such as this would populate a collection with the actual list of direct people:

ClearCollect(directReports, Office365Users.DirectReportsV2(User().Email).value)

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!

@RandyHayes Thanks that code snippet is useful but unfortunately won't work here, because they are senior managers their direct reports are the managers of the users i would like to count so i need to to count their direct reports as well as the direct reports of their direct reports

@StuPower 

Is it only two levels down (user / reports / reports)?  If so, then this formula might be more helpful for you:

 

Clear(directs);
ForAll(Office365Users.DirectReportsV2(User().Email).value, 
       Collect(directs, 
           {user:mail, 
            reports:ShowColumns(Office365Users.DirectReportsV2(mail).value, "mail")}
                 )
           );
ClearCollect(directReports, 
            DropColumns(
                  Ungroup(directs, "reports"), 
                  "user")
            )

After this formula is used, your directReports collection should have the list of all the emails of the people that are direct reports and their direct reports as well.

CountRows(directReports) will be the number of reports and their reports.

 

I hope this is more 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!

@RandyHayes  Thanks alot that works a treat!

 

One issue i do have is that im retrieving blank entries. Putting the collections into gallerys there are entrys with no data which is skewing the count.

 

Is there a way of counting only those that have an email address?

@StuPower 

Hmmm...they should all have an email address, but perhaps you have some users without email?

 

So try to change the formula to this:

Clear(directs);
ForAll(Office365Users.DirectReportsV2(User().Email).value, 
       Collect(directs, 
           {user: displayName, 
            reports: ShowColumns(Office365Users.DirectReportsV2(mail).value, "displayName")}
                 )
           );
ClearCollect(directReports, 
            DropColumns(
                  Ungroup(directs, "reports"), 
                  "user")
            )

This will key off of the Display Name...which hopefully everyone has!

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

@RandyHayes Sorry thats still returning the same number of results, but with no data showing they are now all blank in the gallery, this is only happening on the senior managers direct reports, direct reports

@StuPower 

Hmmm. I hadn't had a chance to fully test the formula until now.  While it appears to work from my side, I did notice there were some levels of information missing.  In the process of figuring that out, I change the formula.  Give this one a try:

Clear(directs);
ForAll(
    Office365Users.DirectReportsV2(User().Email).value,
    Collect(
        directs,
        {
            user: displayName,
            email: mail,
            DirectReport: true
        }
    );
    ForAll(
        Office365Users.DirectReportsV2(mail).value,
        Collect(
            directs,
            {
                user: displayName,
                email: mail,
                DirectReport: false
            }
        )
    )
)

This will give you a collection called directs, that will have all the direct reports and their direct reports.  It also includes a "DirectReport" column that will indicate the first level of direct report if needed.

 

See how that works for you.  Now that I've had a chance to test it through, it seems to work pretty flawless for me.

 

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

@RandyHayesWorks a treat thankyou very much!

Hi, This is really helpful, My need is i will not be knowing the level's when user access the power apps, I would like to count all the users under him till there is no further levels

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Carousel_PP_768x460_Wave2 (1).png

2022 Release Wave 2 Plan

Power Platform release plan for the 2022 release wave 2 describes all new features releasing from October 2022 through March 2023.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Top Solution Authors
Users online (3,507)