cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
StuPower
Level: Powered On

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
Super User
Super User

Re: How to count the number of Office365 users

@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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

8 REPLIES 8
Super User
Super User

Re: How to count the number of Office365 users

@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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
StuPower
Level: Powered On

Re: How to count the number of Office365 users

@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

Super User
Super User

Re: How to count the number of Office365 users

@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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

StuPower
Level: Powered On

Re: How to count the number of Office365 users

@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?

Super User
Super User

Re: How to count the number of Office365 users

@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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
StuPower
Level: Powered On

Re: How to count the number of Office365 users

@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

Super User
Super User

Re: How to count the number of Office365 users

@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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
StuPower
Level: Powered On

Re: How to count the number of Office365 users

@RandyHayesWorks a treat thankyou very much!

Helpful resources

Announcements
thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Kudoed Authors (Last 30 Days)
Users online (3,838)