How to count the number of Office365 users

[StuPower]

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

[RandyHayes]

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

[StuPower]

@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

[RandyHayes · Accepted solution]

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

[StuPower]

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

[RandyHayes]

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

[StuPower]

@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

[RandyHayes]

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

[StuPower]

@RandyHayesWorks a treat thankyou very much!

[Gopinatha]

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