I have a gallery based on a list of people from person_table. I have a sub-gallery inside of that using data from another table (projects_table) that I want to filter by a field (person_code) in the parent gallery, but I can't get it to work.
For the sub-gallery, I have Items set to:
Filter ('[DB].[projects_table]', project_person_code=ThisItem.person_code)
I'm then trying to count the items in projects_table that are active (project_status="A") and display in a label. The label Text is set to:
I get wonky results though, where the count resolves to either '500' or '0' for the different rows, but with no rhyme or reason that I can figure out. Any help would be appreciated.
Solved! Go to Solution.
Create a collection by the GroupBy() on the foreign key in the Projects table, so for example
This creates a collection of two related tables, one with the foreign key (PersonID) as its only column and a second table with the rest of the data that will contain the project details.
Create a Gallery with colProjects as the Items property. Insert a label and it will show the PersonID. Add a second label with the Text property: Lookup(Person_Code,ID=PersonID,PersonName). (you can use this format to add additional labels from your Person_Code table if you wish by replaceing PersonName with other fields) using Lookup() to fill out the rest of the data from the Parent table in the outer gallery.
Add a subgallery inside your first gallery and resize it in your gallery. I like to use a light color fill for this gallery so that it is easy to see and work with. Make the Items property of this gallery: RestofData and add labels for the columns in the Projects table.
In my example, I used the Orders table and created the collection with the command inside of the button to extract the Company ID. I used that collection as the Items property of the outer gallery. The number in the upper left of the gallery is the company ID. I used a lookup to show the company name. The restofdata table was used as the Items property of the subgallery. The data included is the number of items shipped and shipping date/time of the order. You should be able to do something similar with your two tables.
Tip: Make sure that you use Flexible height galleries for both outer and subgallery to account for the different number of items in the subgallery.
I feel like I'm close, but I still can't get it to work. I followed your lead and put the Collect function as you described in OnSelect for a navigation button. It gives me an error saying that the name colProjects isn't recognized and it doesn't do anything when I press the navigation button. Do I have to create the collection first somehow so it recognizes it?
Check it out!
Fill out a quick form to claim your user group badge now!
Find out where you can attend!
Features releasing from October 2019 through March 2020
The largest Power BI, Power Platform, and Data conference in New Zealand