Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Hi Team,
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
Thanks
M Logendran
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
Index | Work Items | Status | Description |
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 | Child |
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 | Measure1 | Measure2 |
Parent 1 | Green | Collected information. |
Child 1.1 | Yellow | Confirm list |
Child 1.2 | Yellow | Blah |
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.
hi, @stewartfiserv
You could use a matrix visual.
Best Regards,
Lin
hi, @stewartfiserv
Could you share some sample data and the format of your expected output.
Best Regards,
Lin
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
87 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
123 | |
107 | |
61 | |
55 |