Forum Discussion
remove historical data from table
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
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
2 Replies
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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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
