Forum Discussion
AdministratorCreate a dynamic filter that is calculated from the selected date
Good
I have a table with the following columns: Patient, Date, Accepted.
| Patient | Date | Accepted |
| 1 | 01/01/2020 | T |
| 1 | 02/01/2020 | T |
| 1 | 03/01/2020 | F |
| 1 | 04/01/2020 | F |
| 2 | 01/01/2020 | F |
| 2 | 01/01/2021 | T |
| 2 | 01/01/2022 | T |
| 2 | 02/01/2022 | T |
I have a date filter included in the report.
I want to create a dynamic filter that calculates if a patient is active, I consider a patient to be active if by the filtered date they have at least one accepted measurement. For example, if I have filtered the date range 01/01/2020 - 31/01/2020 it should appear that patient 1 is active but patient 2 is not, but if I filter 03/01/2020 - 31/01/2021 patient 2 would be the only active one.
How could I do that?
Hi Syndicate_Admin
To achieve a wanted "segmentation" you can apply these steps:
1. You can use a disconnected date table :2. Create Dax measure :
Active check =var min_d = min('calendar'[Date])var max_d = max('calendar'[Date])var count_active =CALCULATE(DISTINCTCOUNT('Table'[Patient]), 'Table'[Date]>=min_d && 'Table'[Date]<=max_d && 'Table'[Accepted]= "T")RETURNif (ISBLANK(count_active),"Not active", "Active")3. use a date table as a filter / slicer
The pbix is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
2 Replies
- Ritaf1983
Super User
Hi Syndicate_Admin
To achieve a wanted "segmentation" you can apply these steps:
1. You can use a disconnected date table :2. Create Dax measure :
Active check =var min_d = min('calendar'[Date])var max_d = max('calendar'[Date])var count_active =CALCULATE(DISTINCTCOUNT('Table'[Patient]), 'Table'[Date]>=min_d && 'Table'[Date]<=max_d && 'Table'[Accepted]= "T")RETURNif (ISBLANK(count_active),"Not active", "Active")3. use a date table as a filter / slicer
The pbix is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
rohit1991Power BI Implementation
Create a Slicer: Add a date slicer for users to select the date range.
Add a Measure: Use this DAX formula to check active patients:
ActivePatients = CALCULATE( DISTINCTCOUNT(TableName[Patient]), TableName[Date] >= MIN('DateTable'[Date]), TableName[Date] <= MAX('DateTable'[Date]), TableName[Accepted] = "T" )Visualize: Add ActivePatients to your report to display active patients dynamically based on the selected date range.