How to Apply Date Filter to only One Category of Data Records

[Anonymous]

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

[v-stephen-msft · Accepted 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.