How can I only show latest updated row for Sequential Data in Power BI/Query

[rouelandrew]

Need help with transforming data in PowerBI. Data is sequential and has a Parent, Child relationship.

1. Sample is in Excel but data is in Smartsheets which I can't manipulate using SQL
2. I've connected PowerBI to Smartsheets and is directly pulling the data from there
3. it contains 1 row for each Parent and that Parent row would have a few columns filled up, but new events within that Parent would be in a Child row, which would have other columns filled up but would have the Customer Name, Customer #, etc. show as blank.
4. Some parent rows would not have other Child rows or events under it
5. Data updates on a regular basis

What I need is to only show the latest row update from the Updates & History Column based on Date of Update when I create a Table Visualization in PBI Desktop.

Raw data looks like this:

In the Table Visualization, the target view is this where the Date of Update and Updates & History columns only show the latest entry/row \when I filter under the Customer Name.

Can this be done in Power Query/BI or using a DAX Formula?

[Anonymous]

Hi @rouelandrew ,

Based on what you have described, you can use Power Query to transform the data and then apply DAX formulas in the visualisation for filtering. Here is a brief plan of action:

To transform data in Power Query

1. Load the data into Power Query.
2. To address the issue where Child rows have blank "Customer Name" and "Customer #" fields, use the "Fill Down" feature in Power Query. This will propagate the Parent's "Customer Name" and "Customer #" down to its Child rows. Select the "Customer Name" and "Customer #" columns, then go to the "Transform" tab and choose "Fill" -> "Down".
3. Apply any filters to exclude unwanted data at this stage if necessary.

Creating calculated columns in Power BI Desktop

1. Use DAX to create a calculated column that identifies the latest "update date" for each parent data.

IsLatestUpdate = 
VAR CurrentDate = [Date of Update]
VAR CurrentParent = [Parent]
RETURN
IF(
    CurrentDate = CALCULATE(MAX([Date of Update]), FILTER(ALL('Table'), [Parent] = CurrentParent)),
    1,
    0
)

Filtering a table visualisation

1. Apply a filter to the table visualisation to only include rows where "IsLatestUpdate" is equal to 1. This will ensure that only the latest update for each parent is displayed.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

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

[rouelandrew]

Hello Ada,

I tried the DAX formula you gave (actual formula below) and instead of assigning 1 for each Parent or Customer Name, it's assigning 1 to every row that is the most recent data. So the total Table that has 3000+ lines has only 15 rows as '1' and it's because all of those rows have Date of Update of March 4, 2024. 

IsLatestUpdate =

VAR CurrentDate = ('CAP/CAT Combined'[Date of Update])

VAR CurrentParent = ('CAP/CAT Combined'[* Parent vs Child])

RETURN

IF(

    CurrentDate = CALCULATE(MAX('CAP/CAT Combined'[Date of Update]), FILTER(ALL('CAP/CAT Combined'), 'CAP/CAT Combined'[* Parent vs Child] = CurrentParent)),

    1,

    0

)