Forum Discussion
Calculate using filter
i have two tables
First table has a date where citations were issued.
Second table has a date where officer was on a certain schedule.
The tables are joined by a unique ibm #
i am trying to filter the data to show what shift the officer wrote the tickets on. I can get total citations written by the officer but if that officer works on a different shift it also produces the total for that shift as a total of the officer
| ibm | offense_date | citation_number |
| jj220 | 01/01/2022 | 1 |
| jj220 | 01/01/2022 | 2 |
| jj220 | 01/02/2022 | 5 |
| aa420 | 01/01/2022 | 3 |
| aa420 | 01/01/2022 | 4 |
| aa420 | 01/02/2022 | 6 |
| ibm | shift | shift start date |
| jj220 | a shift | 01/01/2022 |
| jj220 | a shift | 01/02/2022 |
| aa420 | b shift | 01/01/2022 |
| aa420 | t shift | 01/02/2022 |
Create a calculated column on the Citation table
Shift = var offenseDate = Citations[Offense date] return SELECTCOLUMNS( TOPN( 1, FILTER( RELATEDTABLE( Shifts ), Shifts[Start date] <= offenseDate ), Shifts[Start date], DESC ), "@shift", Shifts[Shift] )
2 Replies
Create a calculated column on the Citation table
Shift = var offenseDate = Citations[Offense date] return SELECTCOLUMNS( TOPN( 1, FILTER( RELATEDTABLE( Shifts ), Shifts[Start date] <= offenseDate ), Shifts[Start date], DESC ), "@shift", Shifts[Shift] )