Forum Discussion
Expandable Table Visual
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
11 Replies
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
hi, Anonymous
You could use a matrix visual.
Best Regards,
Lin
hi, Anonymous
Could you share some sample data and the format of your expected output.
Best Regards,
Lin
Hello Anonymous ,
I want to implement the same visual. Are you able to implement this ?
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.
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
