Forum Discussion
Expandable Table Visual
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.
- beedoubleu2 years agoNew Member
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 - des_san2 years agoAdvocate I
You could try a SWITCH() calculation and define your own heirarchy fields. Here are good examples:
Parent-child hierarchies – DAX Patterns