I would like to display a hierarchy of records in a table format that can display properties for the parent record (that are not aggregates of the child record values) and allow you to expand and collapse the parent record to see the child records.
I would like to display the below table. The status and description values on the parent records are unique to the parent. I would like to be able to expand and collapse the parent records to display or hide the child records.
Is there a way to do this?
Work Items | Status | Description
Parent 1 | Green | Collect and parse information.
Child 1.1 | Yellow | Confirm list of data.
Child 1.2 | Green | Blah blah blah
Parent 2 | Green | Collect and parse information.
Child 2.1 | Yellow | Confirm list of data.
Child 2.2 | Green | Blah blah blah
I'm too have same query but could not get right solution.
Problem of above solutions is by expanding the Parent it not showing its own properties only shows childs but we need parent properties as well
See my solution. Both parent & child are reported on in a matrix visual.
Alternatively, if you need something like a financial statement, I recommend this blog post: Parent-child hierarchies – DAX Patterns
Here is how I would approach this. You'll need two tables, an index column, and two measures.
Table 1; Record Table
|0||Parent 1||Green||Collect and parse information|
|1||Child 1.1||Yellow||Confirm list of data|
|2||Child 1.2||Green||Blah blah blah|
|3||Parent 2||Green||Collect and parse information|
|4||Child 2.1||Yellow||Confirm list of data.|
|5||Child 2.2||Green||Blah blah blah|
Table 2; Work Items Relationship Table
|Parent 1||Parent 1|
|Parent 1||Child 1.1|
|Parent 1||Child 1.2|
|Parent 2||Parent 2|
|Parent 2||Child 2.1|
|Parent 2||Child 2.2|
Note: it is important that the Project record's index value in Table 1 ranks lower (or higher) than the child record for the period you are reporting on.
1. Create a relationship between both tables such that 'Table 2[Child]' is One to Many 'Table 1[Work Items]'.
2. Create a hierarchy field in Table 2 with 'Table 2'[Parent]' and 'Table 2[Child]'
3. Create [Measure1] and [Measure2]. I added them to Table 1.
Measure1 = CALCULATE( MIN('Table 1'[Status]), 'Table 1'[Index] = MIN('Table 1'[Index]) )
Measure2 = CALCULATE( MIN('Table 1'[Description]), 'Table 1'[Index] = MIN('Table 1'[Index]) )
4. Add a matrix table visual to your report. Use the Hierarchy field you created in Table 2 as the Row Header and both [Measure1] and [Measure2] as the values.
You visual should now look like one of these sample matrix tables:
I think this is fairly straight forward, however, let me know if you would like me to expand on any of these concepts.
P.S. the index column can be replaced with another column such as a date or date/time column. But to keep this simple I used an index column to demonstrate how the Measures work.
Thanks for the solution @des_san.
Any thoughts on how you'd not show the parent record when drilling down?
e.g. for example using Sample 2 as an example being able to see something like this:
|Parent 1||Green||Collected information.|
|Child 1.1||Yellow||Confirm list|
|Parent 2||Green||Parse information.|
|Child 2.1||Yellow||list data.|
|Child 2.2||Green||Blah blah|
You could try a SWITCH() calculation and define your own heirarchy fields. Here are good examples:
Parent-child hierarchies – DAX Patterns
I am trying to achieve a similar result. Did anyone find a solution for this? I would like to display a row of data for the parent and then be able to expand out another set of data from a related table.
You could use a matrix visual.
Could you share some sample data and the format of your expected output.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.