Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I have a data set that consists of records that are categorized as either "Active" or "Complete" in a Status field. I would like a date filter on my PowerBI page to only apply to any records that are listed as "Complete", but I would also like to always include all records that are "Active" in the page as well.
For example: If the the date filter is set to (ProjectDate >= 1/5/2023 and Project Date <= 1/24/2023), I would like "Complete" records with a project date that qualifies within the ProjectDate filter range included in the page in addition to all "Active" records regardless of whether the "Active" record's ProjectDate falls within the ProjectDate range or not.
Is this functionality possible within PowerBI?
Thank you,
Paul
Sample data below:
Status | ProjectDate | Value |
Active | 1/5/2023 | 1.176091 |
Complete | 1/6/2023 | 1.20412 |
Active | 1/7/2023 | 1.230449 |
Complete | 1/8/2023 | 1.176091 |
Active | 1/9/2023 | 1.20412 |
Complete | 1/10/2023 | 1.230449 |
Active | 1/11/2023 | 1.176091 |
Complete | 1/12/2023 | 1.20412 |
Active | 1/13/2023 | 1.230449 |
Complete | 1/14/2023 | 1.176091 |
Active | 1/15/2023 | 1.20412 |
Complete | 1/16/2023 | 1.230449 |
Active | 1/17/2023 | 1.176091 |
Complete | 1/18/2023 | 1.20412 |
Active | 1/19/2023 | 1.230449 |
Complete | 1/20/2023 | 1.176091 |
Active | 1/21/2023 | 1.20412 |
Complete | 1/22/2023 | 1.230449 |
Active | 1/23/2023 | 1.176091 |
Complete | 1/24/2023 | 1.20412 |
Active | 1/25/2023 | 1 |
Complete | 1/26/2023 | 2 |
Active | 1/27/2023 | 3 |
Complete | 1/28/2023 | 4 |
Active | 1/29/2023 | 5 |
Complete | 1/30/2023 | 6 |
Active | 1/31/2023 | 7 |
Complete | 2/1/2023 | 8 |
Active | 2/2/2023 | 9 |
Complete | 2/3/2023 | 10 |
Active | 2/4/2023 | 11 |
Complete | 2/5/2023 | 12 |
Active | 2/6/2023 | 13 |
Complete | 2/7/2023 | 14 |
Active | 2/8/2023 | 15 |
Complete | 2/9/2023 | 16 |
Active | 2/10/2023 | 17 |
Complete | 2/11/2023 | 18 |
Active | 2/12/2023 | 19 |
Complete | 2/13/2023 | 20 |
Active | 2/14/2023 | 21 |
Complete | 2/15/2023 | 22 |
Active | 2/16/2023 | 23 |
Complete | 2/17/2023 | 24 |
Active | 2/18/2023 | 25 |
Complete | 2/19/2023 | 26 |
Active | 2/20/2023 | 27 |
Complete | 2/21/2023 | 28 |
Active | 2/22/2023 | 29 |
Complete | 2/23/2023 | 30 |
Active | 2/24/2023 | 31 |
Complete | 2/25/2023 | 32 |
Active | 2/26/2023 | 33 |
Complete | 2/27/2023 | 34 |
Active | 2/28/2023 | 35 |
Solved! Go to Solution.
Hi @Anonymous ,
You cannot set a specific date filter for a status alone in the Filters. However, you can create a separate calendar table for slicer filtering.
Here's my solution.
1.Create a seperate calendar table and it's for the date slicer.
Calendar = CALENDAR(MIN('Table'[ProjectDate]),MAX('Table'[ProjectDate]))
2.Create a measure for visual-level filters. Put it into the visual-level filters and set up show items when the value is 1.
Measure = var _start=MIN('Calendar'[Date])
var _end=MAX('Calendar'[Date])
var _date=MAX('Table'[ProjectDate])
return IF(MAX('Table'[Status])="Active",IF(_date>=_start&&_date<=_end,1),1)
3.Here's the result. Only "Active" dates are filtered, other statuses are not.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
You cannot set a specific date filter for a status alone in the Filters. However, you can create a separate calendar table for slicer filtering.
Here's my solution.
1.Create a seperate calendar table and it's for the date slicer.
Calendar = CALENDAR(MIN('Table'[ProjectDate]),MAX('Table'[ProjectDate]))
2.Create a measure for visual-level filters. Put it into the visual-level filters and set up show items when the value is 1.
Measure = var _start=MIN('Calendar'[Date])
var _end=MAX('Calendar'[Date])
var _date=MAX('Table'[ProjectDate])
return IF(MAX('Table'[Status])="Active",IF(_date>=_start&&_date<=_end,1),1)
3.Here's the result. Only "Active" dates are filtered, other statuses are not.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
90 | |
74 | |
67 | |
63 | |
55 |
User | Count |
---|---|
101 | |
92 | |
74 | |
60 | |
59 |