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.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

View solution in original post

9 REPLIES 9
RandyHayes
Super User III
Super User III

@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.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

@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.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

View solution in original post

@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.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

@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.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

@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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Did you miss the call? Check out the recording here!

Top Solution Authors
Top Kudoed Authors
Users online (20,568)