Power Platform Community Forum Thread Details

This is in Dataverse.

TableName: StaffRole
Columns:
Role is a Choice Column allowing Multiple Values
Staff is a Lookup column to my Staff table (where I can get the FirstName of the person)
Date is a DateOnly column

I need to produce a gallery showing the count of rows for each Role Choice column by the Staff person's First Name so you end up with

From This...

Staff - Role (multiple choice column) - Date
Donald - QC, Spontana - 2024-12-1
Donald - QC - 2024-12-2

Donald - QC - 2024-12-3

Donald - QC, Spotnana - 2024-12-4

Donald - QC - 2024-12-5
Fred - ACD, QC - 2024-12-2

Fred - ACD - 2024-12-3

To This....

Staff - Role - Counts
Donald, QC, 5
Donald, Spotnana, 2
Fred, ACD, 1
Fred, QC, 2

So how do I transform the Role to produce one row per "selected" Role when there are multiple choices.
Then perform a count of rows for each unique Staff, Role combination?

Categories:

Building Power Apps

You can do this in a Canvas App. Of course, if your data set is very large you might want to consider different data models for better performance, perhaps something with roles as another table with an N-N relationship to staff, etc.

I just took the data you provided and threw it in Dataverse for a quick demo.

Step 1. Create a gallery based on the Role options.

I called the multi-select choice set 'Multiple Choice Staff Roles (Demo)'

Step 2. Add a nested gallery that will display the staff names and count for each role.

Setup your primary gallery as you like, it will need a nested gallery to show the staff names for each role.

Step 3. Make a proper formula that a) filters for staff with the current role, b) adds a column to store the staff name plus role name, c) groups the collection by the name of the Staff record.

This formula is filtering the Staff records for those who have a Role "in" (a bit counterintuitive) the current Role value in the primary gallery. A trick here is to put the refrence to the current Role option choice value in array braces [ ].

GroupBy(
    AddColumns(
        Filter(
            Staff,Role in [ThisItem.Value]
        ),
    'Staff Name Plus Role',
    $"{Name} - {ThisItem.Value}"    
    ),
    Name,
    'Staff For Role'
)

Step 4. Display the data in the nested gallery

I do this by just taking the first 'Staff For Role' record in the grouped collection to reference its name plus role value, then add a CountRows on the current 'Staff For Role' grouped collection.

$"{First(ThisItem.'Staff For Role').'Staff Name Plus Role'} ({CountRows(ThisItem.'Staff For Role')})"

It looks like this:

You can also add text fields to sum the values.

Sum for each role's child records:

Sum for the gallery's total:

If this helped you, please click "Does this answer your question" and give it a like to help others in the community (+ close the ticket)!

Power Platform Developer | LinkedIn: Mark Nanneman | Blog: Power Stuff | YouTube: Mark's Power Stuff |