cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anabananei
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
v-qiaqi-msft
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
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

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (42,336)