Answered

How to count the number of Office365 users

(0) Share

Report

Posted on by 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

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (1)

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

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

Was this reply helpful? Yes No
StuPower 57 on at

Like (0)

Report

@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

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

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

Was this reply helpful? Yes No
StuPower 57 on at

Like (0)

Report

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

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

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

Was this reply helpful? Yes No
StuPower 57 on at

Like (0)

Report

@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

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

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

Was this reply helpful? Yes No
StuPower 57 on at

Like (1)

Report

@RandyHayesWorks a treat thankyou very much!

Was this reply helpful? Yes No
Gopinatha 2 on at

Like (0)

Report

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

Was this reply helpful? Yes No