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

Group By and CountRows for Task Dashboard

Hello,

 

I'm trying to create a dashboard in Powerapps using a gallery to show my team's current workload and task allocations.

 

What I'm trying to achieve is to have their Pictures and names as distinct entry in gallery then below will have the count of their current task at hand. I would also like this to be filtered by start and end dates.

 

My Data source is a sharepoint list/collection with the following column:

 

1. AssignedTo - People/Group Column

2. Status - Choices

 a. In Progress

 b. Deferred

 c. Quality Check

 d. Waiting for approval

I tried the following formula below but I can't get to show the personfield.picture, personfield.displayname, and status.value but instead it gives me an option to choose on Personfield.DataCard and PersonField.Column1. Using both does not give me the Picture nor Displayname property.

AddColumns(
AddColumns(
GroupBy(
AddColumns(
     Filter(tablename,(startdate>=datepicker1.SelectedDate,startdate<=datepicker2.SelectedDate)||
                      (enddate>=datepicker1.SelectedDate,enddate<=datepicker2.SelectedDate)
            ),
     "userimage",
     personfield.Picture,
     "username",
     personfield.DisplayName,
     "taskstatus",
     status.Value
            )
"username",
"userimage",
"taskstatus",
"tasks"),
"num",
CountRows(tasks)
),
"tasknum",
Concatenate(taskstatus,":",num)
)

 

Please let me know your thoughts.
Thanks!

4 REPLIES 4
Community Support
Community Support

Hi@anabananei,

Based on the issue that you mentioned, do you want to display user photos and names within a Gallery?

Could you please share a bit more about your scenario, how you set the Image property of the Image control?

In general, if you want to display user photo within a Gallery, you just need to set the Image property of the Image as below:

 

ThisItem.personfield.Picture

 

082801.png

If you want to display user photos and names, except for the SP list connection, you should also add the Office365Users connection within your App. Further, the "Office365 Users" connector has several functions to search users, get user profiles, get user photos, etc.

To display user photo, we set Image property of Image as below:

 

If(
 !IsBlank(ThisItem.PersonFieldName.Email), 
  Office365Users.UserPhotoV2(ThisItem.PersonFieldName.Email)
)

 

082802.png

As for the formula you provided, I have a test on my side and there is something syntax wrong with your Filter() function, please modify your formula within the Items property of your Gallery as below: 

 

AddColumns(
    AddColumns(
        GroupBy(
            AddColumns(
                Filter(
                    APAC1,
                    (startdate >= DatePicker1.SelectedDate && startdate <= DatePicker2.SelectedDate) || (enddate >= DatePicker1.SelectedDate && enddate <= DatePicker2.SelectedDate)
                ),
                "userimage",
                'AssignedTo '.Picture,
                "username",
                'AssignedTo '.DisplayName,
                "taskstatus",
                Status.Value
            ),
            "username",
            "userimage",
            "taskstatus",
            "tasks"
        ),
        "num",
        CountRows(tasks)
    ),
    "tasknum",
    Concatenate(
        taskstatus,
        ":",
        "num"
    )
)

 

Note: 'APAC1' is my SP list, you can replace it or other column names, controls with yours within your App.

And If you want to display "tasknum" within your Gallery, you just need to insert a Label and set the Text property as below:

 

ThisItem.tasknum  *// You can replace it with whatever you want to display

 

082803.png

Best Regards,

Qi

Hello @v-qiaqi-msft ,

 

What I want to achieve is to get the record from the SharePoint list. In that list, different users has different tasks and I want to display into a dashboard their workload per assignee. However, When I link it to the gallery, it duplicates the assignee names and assignee pictures, then their tasks. In short, I envisioned that Assignee will be somewhat distinct then their workload is consolidated per user.

 

Example:

 

Gallery Item 1:

Assignee: User 1

In Progress: 2

Quality Check: 1

Waiting for Approval: 2

 

Gallery Item 2:

Assignee: User 2

In Progress: 1

Quality Check: 0

Waiting for Approval: 3

 

....................

 

 

I will try to follow your suggestion and will let you know of the result.

 

Thank you!

Hi@anabananei,

Hope my solution could help.

Yes, what you want could be achieved within my solution. You need to insert several Labels into Gallery and set the Text property of them to any field name you want to display.

Feel free to contact me.

Best Regards,

Qi

Hi @v-qiaqi-msft ,

 

It worked. However, it gives you another entry of the same user if they have different status. See below picture:

01-09-2020 3-46-55 PM.png

 

Instead of two separate entries for John B., is it possible to show this:

 

John B.

In Progress: 1

Quality Check: 2

----------------------------------------------

John A.

In Progress: 0

Quality Check: 1

 

Also, I had to tweak the Concatenate function at the end to give me a proper task count instead of the value "num"

Concatenate(
        taskstatus,
        ":",
        Text(num)
    )

 

Thank you very much.

Helpful resources

Announcements
News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Top Solution Authors
Top Kudoed Authors
Users online (3,698)