Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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
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
User | Count |
---|---|
54 | |
22 | |
19 | |
16 | |
11 |
User | Count |
---|---|
81 | |
55 | |
39 | |
20 | |
12 |