cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

How to deal with recursive parent and grouping?

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_idEmployee_nameDirectorsTopManagementMiddleManagementFirstLineManagement
1BobAlice   
2RyanKimmyRosy  
3JamesAliceBen Gary
4BryanDavid Will 

WorkGroups:

WG_idEmployeeWG_idParentEmployee_idWG_name
124research_1
224research_16
3114top_secret_research
446top_secret_research
546experts_research_2

Project:

Project_idWGProject_idProject_name
14ultrasound_prjt
24ovni_prjt
35quantum_physics_prjt
41infrared_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 
EmployeeProject
Aliceultrasound_prjt
 ovni_prjt
Bobultrasound_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:

 

tablix.png

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)

 

tablix_group.png

 

 

 

 

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!

 

0 REPLIES 0

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors