Expandable Table Visual

[stewartfiserv]

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

[Anonymous]

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

[des_san]

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

[des_san]

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.

[beedoubleu]

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_san]

You could try a SWITCH() calculation and define your own heirarchy fields. Here are good examples:
Parent-child hierarchies – DAX Patterns

[daniel_digi]

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. 

[Anonymous]

@stewartfiserv , did you get this figured out?

[saurabhpore]

Hello @stewartfiserv ,

I want to implement the same visual. Are you able to implement this ?

[v-lili6-msft]

hi, @stewartfiserv 

You could use a matrix visual.

Best Regards,

Lin

[stewartfiserv]

Unfortunately that doesn’t work. You can’t display unique properties for the parent records. It doesn’t meet the goal I outlined with being able to see multiple properties for the parent record and still expand and see child records.

[v-lili6-msft]

hi, @stewartfiserv 

Could you share some sample data and the format of your expected output.

Best Regards,

Lin