I only want my date filter to affect closed tasks

[ThomasWeppler]

Hi power Bi community

I have a table [Tasks] with the following columns. 

Index (number): An index

taskid (number): a uniq number for each task

Isdeleted (true/false): is the task deleted

isdeleted date (date): the date the task was deleted. It shows blank if the task is still active.

I want a report that shows all the active tasks and the deleted tasks in the selected periode.

Does people have any idea of how they would solve this in DAX or M?

All help is greatly appreciated.

[Anonymous · Accepted solution]

Hi @ThomasWeppler ,

I will show you the sample data first:

You can then use the following two DAXs to create two new tables that extract the True and False records respectively:

Mark_False = 
FILTER(
    'Tasks',
    'Tasks'[Isdeleted] = FALSE
)

Mark_True = 
FILTER(
    'Tasks',
    'Tasks'[Isdeleted] = TRUE
)

Then I created a date table:

Table = CALENDAR(DATE(2023,1,1), TODAY())

Create a relationship between the date table and the Isdeleted date in the table Mark_True:

Create a slicer using the date table you created:

Best Regards,

Dino Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.