Forum Discussion
Sort table on last - shows wrong result
I have a table that looks like this
| Status (TEXT) | Date (Date and Time) | Customer (TEXT) |
| Active | 2024-05-17 11:30:49 | Customer A |
| Inactive | 2024-05-17 09:27:12 | Customer A |
In powerbi i want to create a table that display my rows.
This works.
Then i want to filter on last status
It works but it mixes things up.
If i select Last.
Then the powerbi table looks like this:
| Status (TEXT) | Date (Date and Time) | Customer (TEXT) |
| Inactive | 2024-05-17 11:30:49 | Customer A |
I dont understand why it now shows the date connected to the wrong status....
Its like the date and time column is not working.....
any tips on how to fix this?
It's because it's displaying the "Last" value alphabetically, rather than by the status time. You need to use a measure in this case to grab the latest status (and customer too!) The reason the date works is because it is technically stored as a number - so it makes sense to grab the biggest number in the series of dates.
Here's the measure i used to grab the latest status - you can easily modify to grab the latest customer:
Latest Status = CALCULATE(MAX('Table (2)'[Status (TEXT)]), 'Table (2)'[Date (Date and Time)] = MAX('Table (2)'[Date (Date and Time)]))
1 Reply
It's because it's displaying the "Last" value alphabetically, rather than by the status time. You need to use a measure in this case to grab the latest status (and customer too!) The reason the date works is because it is technically stored as a number - so it makes sense to grab the biggest number in the series of dates.
Here's the measure i used to grab the latest status - you can easily modify to grab the latest customer:
Latest Status = CALCULATE(MAX('Table (2)'[Status (TEXT)]), 'Table (2)'[Date (Date and Time)] = MAX('Table (2)'[Date (Date and Time)]))