remove historical data from table

[salil]

Hi, 

I am facing issue in creating Table 2 from Table 1. Table 1 has historical records for the host. I want to keep the latest row data for each host in Table 2.

please refer Table 1

Host	Date	Status
Computer1	30/11/2022	Compliant
Computer2	30/10/2022	Non Compliant
Computer3	30/06/2022	Compliant
Computer1	25/11/2022	Compliant
Computer2	25/09/2022	Compliant
Computer3	25/07/2022	Non Compliant

table 2 output should be 

Host	Date	Status
Computer1	30/11/2022	Compliant
Computer2	30/10/2022	Non Compliant
Computer3	25/07/2022	Non Compliant

I am able to get the unique value for the Host column but getting the latest date and related status is showing error.

regards

Salil Singh

[v-yanjiang-msft]

Hi @salil ,

If you want a seperate table, @amitchandak 's solution works fine. If you want a visual to display the expected result, here's my solution. Create a measure:

Measure =
IF (
    MAX ( 'Table'[Date] )
        = MAXX (
            FILTER ( ALL ( 'Table' ), 'Table'[Host] = MAX ( 'Table'[Host] ) ),
            'Table'[Date]
        ),
    1,
    0
)

Put the measure in the visual filter and set to 1.

After apply filter, get the correct result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[amitchandak · Accepted solution]

@salil , a new table

Filter(AddColumns(Table1, "Max Date", maxx(filter(Table, [Host] = earlier([Host])), [Date])), [Date] = [Max Date])

Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...