How to deal with recursive parent and grouping?

[jeanmarie77]

Hello everyone!

I'm new to Power BI/Power BI Report Builder but keen to learn new concepts and tips with these wonderful tools!

So, I'm trying to create a PDF report using Power BI Report Builder.

I have 3 datasets which look like this:

Employee:

Employee_id	Employee_name	Directors	TopManagement	MiddleManagement	FirstLineManagement
1	Bob	Alice
2	Ryan	Kimmy	Rosy
3	James	Alice	Ben		Gary
4	Bryan	David		Will

WorkGroups:

WG_id	EmployeeWG_id	ParentEmployee_id	WG_name
1	2	4	research_1
2	2	4	research_16
3	1	14	top_secret_research
4	4	6	top_secret_research
5	4	6	experts_research_2

Project:

Project_id	WGProject_id	Project_name
1	4	ultrasound_prjt
2	4	ovni_prjt
3	5	quantum_physics_prjt
4	1	infrared_prjt

Basically, Employee is a table containing each employee along with their superiors. Each employee is part of one or more WorkGroups and each WorkGroup is assigned to one or more Project.

On Report Builder, I would like to have a table which displays each WorkGroup with the organisational hierarchy of employees being part of this group and also lists every project they're working on. So it would look like something like this for example:

top_secret_research
Employee	Project
Alice	ultrasound_prjt
	ovni_prjt
Bob	ultrasound_prjt
	ovni_prjt
	quantum_physics_prjt

(the row under Alice and Bob should be merged with it but I'm not able to do it here..)

On Report Builder, I used a tablix which is bounded to the WorkGroups dataset. I'm displaying the WG_name on the first row and this cell is a group on WG_name. On the left part of the next row, I have a child group on the EmployeeWG_id which also has a recursive parent on ParentEmployee_id, so that it can displays the hierarchy. So far, everything works, but I'm struggling to understand how to dynamically add the corresponding projects on the right part of that row. I tried adding another child group but it returns every distinct project for each employee. I also tried changing the Tablix to a Matrix but it doesn't change anything.

I'm sharing a screen shot of my Tablix and the groups associated with it:

Here, the first expression under "Employee" allows me to get the Employee_name from the Employee dataset (with Lookup) and the second expression under "Projet" is supposed to return every project of each employee (with LookupSet)

I'm probably confused with grouping and recursive parent handling. I'd like to keep the datasets as they're if that's possible.

Thank you for your help and have a nice day!