Forum Discussion
Data filtering based on higher value in column
Hello all,
I would like to filter the table below based on the higher value in the Order ID for each row where the Unique ID is matching.
The table below represents a history that's why there is the same Unique ID for rows, however, the higher value of Order ID means that it's the latest output.
| AA ID | DB ID | Status | Order ID | Unique ID |
| 1111 | 2222 | Failed | 100 | 123456 |
| 1111 | 2222 | Finished | 101 | 123456 |
| 2222 | 3333 | Exception | 110 | 234567 |
| 2222 | 3333 | Rescan | 111 | 234567 |
| 2222 | 3333 | Finished | 112 | 234567 |
| 3333 | 1234 | Exception | 120 | 987654 |
As an output table from the example above I would have:
| AA ID | DB ID | Status | Order ID | Unique ID |
| 1111 | 2222 | Finished | 101 | 123456 |
| 2222 | 3333 | Finished | 112 | 234567 |
| 3333 | 1234 | Exception | 120 | 987654 |
Thanks for your help!
Anonymous
Create a new table with the following filter:Filtered Order Table = FILTER( Table2, var __maxorder = CALCULATE( MAX(Table2[Order ID]) , ALLEXCEPT( Table2 , Table2[AA ID] )) return Table2[Order ID] = __maxorder )
2 Replies
Anonymous
Create a new table with the following filter:Filtered Order Table = FILTER( Table2, var __maxorder = CALCULATE( MAX(Table2[Order ID]) , ALLEXCEPT( Table2 , Table2[AA ID] )) return Table2[Order ID] = __maxorder )