Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
64 | |
63 |
User | Count |
---|---|
142 | |
105 | |
102 | |
80 | |
67 |