Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ThomasWeppler
Skilled Sharer
Skilled Sharer

I only want my date filter to affect closed tasks

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.

 

1 ACCEPTED SOLUTION
v-junyant-msft
Community Support
Community Support

Hi @ThomasWeppler ,

I will show you the sample data first:

vjunyantmsft_0-1700552895040.png

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
)

vjunyantmsft_1-1700553023038.png

Then I created a date table:

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

vjunyantmsft_2-1700553072991.png

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

vjunyantmsft_3-1700553110288.png
Create a slicer using the date table you created:

vjunyantmsft_4-1700553296244.png


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.

View solution in original post

1 REPLY 1
v-junyant-msft
Community Support
Community Support

Hi @ThomasWeppler ,

I will show you the sample data first:

vjunyantmsft_0-1700552895040.png

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
)

vjunyantmsft_1-1700553023038.png

Then I created a date table:

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

vjunyantmsft_2-1700553072991.png

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

vjunyantmsft_3-1700553110288.png
Create a slicer using the date table you created:

vjunyantmsft_4-1700553296244.png


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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.