Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
stewartfiserv
Frequent Visitor

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 11
Anonymous
Not applicable

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

Logendran_0-1672790789058.png

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

des_san
Advocate I
Advocate I

Here is how I would approach this. You'll need two tables, an index column, and two measures.

 

Table 1; Record Table

IndexWork ItemsStatusDescription
0Parent 1GreenCollect and parse information
1Child 1.1YellowConfirm list of data
2Child 1.2GreenBlah blah blah
3Parent 2GreenCollect and parse information
4Child 2.1YellowConfirm list of data.
5Child 2.2GreenBlah blah blah

 

Table 2; Work Items Relationship Table

ParentChild
Parent 1Parent 1
Parent 1Child 1.1
Parent 1Child 1.2
Parent 2Parent 2
Parent 2Child 2.1
Parent 2Child 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.

 

des_san_1-1659129414637.png

 

 

You visual should now look like one of these sample matrix tables:

des_san_0-1659129281664.png

 

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:

ParentMeasure1Measure2
Parent 1GreenCollected information.
  Child 1.1YellowConfirm list
  Child 1.2YellowBlah
Parent 2GreenParse information.
  Child 2.1Yellowlist data.
  Child 2.2GreenBlah blah

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

daniel_digi
New Member

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
Not applicable

@stewartfiserv , did you get this figured out?

Hello @stewartfiserv ,

 

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

 

v-lili6-msft
Community Support
Community Support

hi, @stewartfiserv 

You could use a matrix visual.

1.JPG

2.JPG

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

hi, @stewartfiserv 

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

 

Best Regards,

Lin

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.