Forum Discussion
Get last sequences for a value
Hi Experts,
I'm having trouble solving an issue that seems simple but I can't find the solution. I have a table where I have the following columns:
| Sinister | Sequence | Date | Patient | Episode | Type of Coverage |
| 1000 | 1 | 01-01-2022 | Juan | 1 | 2 |
| 1000 | 2 | 05-01-2022 | Juan | 2 | 4 |
| 1000 | 3 | 02-02-2022 | Juan | 3 | 1 |
| 1000 | 4 | 15-02-2022 | Juan | 4 | 4 |
| 1001 | 1 | 02-02-2022 | Pedro | 1 | 4 |
| 1001 | 2 | 15-01-2022 | Pedro | 4 | 2 |
| 1001 | 3 | 02-02-2022 | Pedro | 5 | 4 |
| 1000 | 5 | 01-03-2022 | Juan | 4 | 8 |
I need for a range of dates to show the last Sequence (Column B) for the same Sinister (Column A), and then filter by Type of Coverage (Column F). For example:
Example 1:
If in the date slicer I select 01-01-2022 to 01-31-2022, only row 4 should be displayed in the matrix/table. Why?
- For Sinister 1000, the last sequence is 2 and type of coverage is 4
- For Sinister 1001, the last sequence is 2 but the Type of Coverage is 2.
Example 2:
If in the date slicer I select 01-01-2022 to 28-02-2022, only row 5 and 8 should be displayed in the matrix/table. Why?
- For Sinister 1000, the last sequence is 4 and type of coverage is 4.
- For Sinister 1001, the last sequence is 3 and type of coverage is 4.
Any help is welcome.
Greetings,
LM
Hi ldmass ,
Here are the steps you can follow：
1. Create calculated table.
Date = CALENDAR( DATE(2022,1,1),DATE(2022,12,31))2. Create measure.
Flag = var _mindate=MINX(ALLSELECTED('Date'),[Date]) var _maxdate=MAXX(ALLSELECTED('Date'),[Date]) var _max= MAXX(FILTER(ALLSELECTED('Table'), 'Table'[Date]>=_mindate&&'Table'[Date]<=_maxdate&&'Table'[Sinister]=MAX('Table'[Sinister])),[Sequence]) return IF( MAX('Table'[Sequence])=_max,1,0)3. Place [Flag]in Filters, set is=1, apply filter.
4. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
1 Reply
Hi ldmass ,
Here are the steps you can follow：
1. Create calculated table.
Date = CALENDAR( DATE(2022,1,1),DATE(2022,12,31))2. Create measure.
Flag = var _mindate=MINX(ALLSELECTED('Date'),[Date]) var _maxdate=MAXX(ALLSELECTED('Date'),[Date]) var _max= MAXX(FILTER(ALLSELECTED('Table'), 'Table'[Date]>=_mindate&&'Table'[Date]<=_maxdate&&'Table'[Sinister]=MAX('Table'[Sinister])),[Sequence]) return IF( MAX('Table'[Sequence])=_max,1,0)3. Place [Flag]in Filters, set is=1, apply filter.
4. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly