date slicer setup

[Anonymous]

<p> I have a date table and another Project data table with Planned start and Planned Finish. How to connect these two tables..so that if i put a slicer for date table, it picks and filters correct data. Currently its not filtering all datas.

</P>

Sl.No	Project Name	Planned Start	Planned Finish
1	a	18/07/2022	19/08/2022
2	b	20/08/2022	20/09/2022
3	c	20/08/2022	20/09/2022
4	d	21/09/2022	21/10/2022

[v-easonf-msft]

Hi, @Anonymous 

If you have a seperate calendar table ,you can create a measure and apply it to table filter pane to filter data.

visual filter = 
VAR _selecteddate =
    MAX ( 'Calendar'[Date] )
RETURN
    IF (
        ISFILTERED ( 'Calendar'[Date] ),
        IF (
            _selecteddate >= MAX ( 'Table'[Planned Start] )
                && _selecteddate <= MAX ( 'Table'[Planned Finish] ),
            1,
            0
        ),
        1
    )

Best Regards,
Community Support Team _ Eason

[Anonymous]

and also its not working on Cards visual

[Anonymous]

if date filter is a list..its filtering the data. But when the date filter is changed to hierarchy like QTR , month..its not filtering all datas

[Anonymous]

Hi @Anonymous 

That is a cross join and you will have all rows from second table for each row in the first table and Vice-versa.

The join needs to be on the date column. 

[Anonymous]

I didnt understand the solution . Could you please help to understand more clearly. Thanks

[Anonymous]

HI @Anonymous 

How does your current relationship between table looks like?

[Anonymous]

I have added a column tag=1 in date table and as well in Project data table. Set relationship as Many - Many

Date(tag)=Projectdata(Tag)