cant see how to indent historic data in a computer compliance list

[db042190]

hi we have a flattened dataset whose core column is computer name.   every month, attributes and facts about that computer are recorded.   Examples might be free space, os version etc etc.

i' d like to show the history of these attributes indented beneath computer name in last in first out order by attribute recording date.  And leave the line showing the computer name blank to the right of the computer name.   i'd also like a collapse expand button for the computer name.  

i dont have any natural hierarchies.  when i tried building the matrix tile, the biggest problem was that i was getting only one line of attributes for a computer i think with earliest "recorded" date.  i might even be ok if the latest attributes showed on the same line as the computer name so in collapsed mode for that computer, the latest set of compliance info would still show.

the first image below shows my table record format.   jobid and date inserted would have more to do with how the record was added by our etl and wouldnt show in the matrix.  The second image shows where i started in pbi and realized i didnt know how to do this.   i started reading articles but so far they seem to strat past the point where the indenting/formatting is set up.   im going to try creating a hierarchy from computer to date recorded but dont have a lot of confidence.   

[Anonymous]

Hi,DataNinja777 ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.

Hello,@db042190 .I am glad to help you.

The matrix in Power BI displays aggregated values, so you need to change your layout in the matrix by trying to place multiple hierarchical data in the 'rows' area. In the 'value' area, place a measure of the aggregation effect.
Like this
measure = SUM('Table'[computer name])
The measure is affected by the rows and displays the value of the current row (which is still actually an aggregated value, but the current row only has one value to be aggregated, so it returns it's self)

I hope this issue was helpful. In it I describe the basic format of the data structure needed by Power BI (multiple fields with containment relationships between them)
After the data of such type of fields is placed into the row/column dimension of the matrix, the system automatically creates the hierarchy based on the containment relationship.
URL:
Solved: Use name column in parent-child hierarchy - Microsoft Fabric Community
 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataNinja777 · Accepted solution]

Hi @db042190 ,

To achieve your desired indented format with expandable and collapsible rows in Power BI, you can structure your data into a hierarchy consisting of Computer Name, Date Recorded, and Attribute. Since there are no natural hierarchies, you can manually create one by dragging Date Recorded under Computer Name and then placing Attribute under Date Recorded in the matrix visual.

In the matrix visual, ensure that "Stepped Layout" is enabled to achieve the indentation effect. This allows the data to be displayed in a structured format where the computer name serves as the parent node, and the recorded dates and attributes appear as child nodes beneath it. However, one common issue with Power BI's matrix visual is that it might display only the earliest recorded entry. To address this, sorting Date Recorded in descending order will ensure that the most recent attributes appear first when expanded.

For the latest attributes to be displayed even when the row is collapsed, you need a measure to fetch the latest available values. You can use the following DAX formula:

Latest Value = 
VAR LatestDate = 
    CALCULATE(
        MAX('Table'[Date Recorded]),
        ALLEXCEPT('Table', 'Table'[Computer Name], 'Table'[Attribute])
    )
RETURN 
CALCULATE(
    SELECTEDVALUE('Table'[Value]),
    'Table'[Date Recorded] = LatestDate
)

This measure will always return the most recent value for each attribute and will be displayed in the matrix when the computer name row is collapsed. This way, the latest compliance data is still visible while maintaining the ability to expand and view historical records.

If you want to leave the computer name row blank in the matrix to maintain a cleaner look, Power BI does not natively allow this, but a workaround is to create a calculated column that only displays the computer name when the row is not indented:

ShowComputer = IF(ISINSCOPE('Table'[Date Recorded]), BLANK(), 'Table'[Computer Name])

Replacing the original Computer Name field in the matrix with this calculated column will result in a blank row when viewing the history beneath each computer name.

With this setup, the matrix will display computer names with an expandable and collapsible history of attributes. When collapsed, it will show only the most recent recorded data, while expanding will reveal older records in a structured and indented format.

Best regards,