I have a table that captures the history of application references as below. Each time an application reference has a status change a new record is created with a corresponding unique ID.
| Application Reference | ID | Load Date | Status |
| 1 | 1 | 01/01/2023 | A |
| 1 | 2 | 02/01/2023 | B |
| 1 | 3 | 03/01/2023 | C |
| 1 | 4 | 04/01/2023 | B |
| 1 | 5 | 05/01/2023 | A |
| 2 | 6 | 01/01/2023 | A |
| 2 | 7 | 02/01/2023 | B |
| 2 | 8 | 03/01/2023 | B |
| 2 | 9 | 04/01/2023 | C |
| 2 | 10 | 05/01/2023 | A |
I have created a measure to dynamically show the latest ID (based on the load date) for each reference only based on a load date slicer (e.g. if we use an old date it shows the latest ID up until that date). I used the measure below:
Latest Status 2 = var Max_Date= CALCULATE(MAX(Sheet1[Load Date]),FILTER(ALLSELECTED(Sheet1),Sheet1[Application Reference]=MAX(Sheet1[Application Reference]))) return
CALCULATE(MAX(Sheet1[ID]), FILTER(Sheet1,Sheet1[Load Date] = Max_Date))
So, this works great and lets users see the latest status based on the dates they select. However, I would like this measure to not respond to an external status slicer. e.g. if we select a date range witht the end date 05/01/2023 the statuses shown should be A (for both application reference 1 and 2). But if we then slice the data by status B, the visual should still show 2 records for status A (i.e. not respond to an external status filter, but respond to the load date filter).
