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 ,
Adjust to the below:
TEST = var maxdate=CALCULATE (
MAX ( 'Table'[Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[Sinister] = MAX ( 'Table'[Sinister] )
&& 'Table'[Date] >= CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
&& 'Table'[Date] <= CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
)
) var pd1= IF (
CALCULATE (
MAX ( 'Table'[Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[Sinister] = MAX ( 'Table'[Sinister] )
&& 'Table'[Date] >= CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
&& 'Table'[Date] <= CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
)
)
= MAX ( 'Table'[Date] ),
1,
0
) var pd2=IF(MAX('Table'[Sequence])=
CALCULATE (
MAX ( 'Table'[Sequence]),
FILTER (
ALL ( 'Table' ),
'Table'[Sinister] = MAX ( 'Table'[Sinister] )&&'Table'[Date]= maxdate)),1,0) return IF(PD1=1&&pd2=1,1,0)
Result:
Best Regards
Lucien
HI @ldmass ,
Pls use the below measure ,then filter the measure:
TEST =
IF (
CALCULATE (
MAX ( 'Table'[Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[Sinister] = MAX ( 'Table'[Sinister] )
&& 'Table'[Date] >= CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
&& 'Table'[Date] <= CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
)
)
= MAX ( 'Table'[Date] ),
1,
0
)
Output result:(the second slicer ,with two same last date on 1001,so with the two result)
Best Regards
Lucien
Hi Lucien,
Thanks you for your answer. In the second image, only sequence 3 should be displayed, since it is the largest, regardless of the date. For the selected date range, only the largest sequence should be displayed
HI @ldmass ,
Adjust to the below:
TEST = var maxdate=CALCULATE (
MAX ( 'Table'[Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[Sinister] = MAX ( 'Table'[Sinister] )
&& 'Table'[Date] >= CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
&& 'Table'[Date] <= CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
)
) var pd1= IF (
CALCULATE (
MAX ( 'Table'[Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[Sinister] = MAX ( 'Table'[Sinister] )
&& 'Table'[Date] >= CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
&& 'Table'[Date] <= CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
)
)
= MAX ( 'Table'[Date] ),
1,
0
) var pd2=IF(MAX('Table'[Sequence])=
CALCULATE (
MAX ( 'Table'[Sequence]),
FILTER (
ALL ( 'Table' ),
'Table'[Sinister] = MAX ( 'Table'[Sinister] )&&'Table'[Date]= maxdate)),1,0) return IF(PD1=1&&pd2=1,1,0)
Result:
Best Regards
Lucien
User | Count |
---|---|
101 | |
91 | |
83 | |
76 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |