Hi Experts,
I’m a DAX newbie and could use some assistance with slicer selected dates.
The slicer is based on our calendar table which is joined to the Service_Activity_Log_Table.
Service_Activity_Log_Table snippet:
Patient Name | Service Date | Service | Service Provider | Program Alignment Service |
A | 5/2/2022 | 1A | Pam | BHH |
B | 5/18/2022 | 1A | Pam | CSM |
A | 6/10/2022 | 3A | Linda | BHH |
C | 6/10/2022 | 1A | Sue | BHH |
A | 6/12/2022 | 2C | Linda | CSM |
C | 6/15/2022 | 2C | Pam | BHH |
B | 7/2/2022 | 1A | Pam | CSM |
C | 7/13/2022 | 2C | Sue | BHH |
A | 7/15/2022 | 1A | Linda | BHH |
The user requested the option to select a date range of “Service Dates” and then view the column details of Service Activity Log for only the selected dates.
The user expects to also see in the same detail table all patients enrolled in the BHH Program. This data is stored in the Patient_Program_Alignment_Table with start and end dates.
Patient_Program_Alignment_Table snippet:
Patient Name | BHH Alignment Start | BHH Alignment Expire |
A | 4/28/2022 | |
B | 6/1/2022 | 7/1/2022 |
D | 5/10/2022 | |
E | 5/1/2022 | 7/10/2022 |
F | 6/25/2022 | |
G | 1/4/2022 | 5/1/2022 |
The expected table visual would have the following rows for date slicer selection June 1, 2022 – June 30, 2022:
BHH Patients | Service Date | Service | Service Provider | Program Alignment Service | Program Alignment |
A | 6/10/2022 | 3A | Linda | BHH | BHH |
C | 6/10/2022 | 1A | Sue | BHH | |
C | 6/15/2022 | 2C | Pam | BHH | |
E | | | | | BHH |
F | | | | | BHH |
A | 6/12/2022 | 2C | Linda | CSM | BHH |
Its simple to display the Service_Activity_Log_Table data based upon the slicer dates. The issue as I see it is finding those patients without services and are aligned to the BHH program.
I can select the patients aligned with BHH program using a slicer with for Alignment Start Date from the Patient_Alignment_Table. Here’s the DAX formula to display the BHH aligned patients:
BHH Alignment in Period =
CALCULATE(COUNTROWS(CONSUMER_PROGRAM_BHH_ALIGNMENTS_TABLE),
Filter(VALUES(CONSUMER_PROGRAM_BHH_ALIGNMENTS_TABLE[Alignment_Start_Date]),
CONSUMER_PROGRAM_BHH_ALIGNMENTS_TABLE[Alignment_Start_Date] <=MAX(DATE_DIMENSION_TABLE[DimDate]) ),
FILTER(VALUES(CONSUMER_PROGRAM_BHH_ALIGNMENTS_TABLE[Alignment_End_Date]),
OR(CONSUMER_PROGRAM_BHH_ALIGNMENTS_TABLE[Alignment_End_Date] >= MIN(DATE_DIMENSION_TABLE[DimDate]), ISBLANK(CONSUMER_PROGRAM_BHH_ALIGNMENTS_TABLE[Alignment_End_Date]))))
The user is expecting all aligned BHH patients with or without services in the selected slicer Service dates. Thank you in advance for any advice/assistance - Marianne
