Using FILTER to filter a table based on a SELECTEDVALUE(date) does not work

[JSher]

I have a page where I have a date from a table(Table1) selected from a drillthrough.

I create a measure:

SelectedDate = SELECTEDVALUE(Table[Date])

This measure when inserted into a card produces: 01/06/2021 12:00:00 AM

I have another table called Table2 with no filters applied to it. 

If I produce just the table in a table visual it shows all the records.

Table2 has a date field called Date2

I am trying to filter Table2 using the Date2 field being greater than the SelectedDate measure like this:

FilteredTable = FILTER(Table2,Table2[Date2]>SelectedDate)

This does not work and I still get all the records.

If I use this:

FilteredTable = FILTER(Table2,Table2[Date2]>DATE(2021,06,01))

It works.

So if the SelectedDate is the same as the date, why is the measure not working???

Thanks

[ERD]

Hi @JSher ,

1. Are your tables connected via relations?

2. Are you trying to create a calculated table based on Table2? If so, keep in mind that measures are dynamic, but calculated columns/tables are computed during the database processing and then stored in the model. 

You need to either connect your tables, or to use measures.

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

[JSher]

My tables are not connected as they are not related. The user drills through using one table and then I need to display information from another table based on the date of the drill through.

I am just trying to filter the table based on a date measure(date is from another table). The date measure displays fine in a card but when I use it to filter it does not work.

So what your saying is the filtered table is calculated prior to the drillthrough and thats why the static date works and they dynamic one will not?

If so how do I get around this?

[Samarth_18]

Hi @JSher 

Could you please check the date format of both tables

[JSher]

Table 1 is Date Data type Format of March14,2001

Table 2 is Date/time Data type Format of 2001-03-14 1:30:55 PM

Should they be the same?  I changed the measure to match the format of Table 2

EDIT:

I switched them all to the same date format 2001-01-15 and it did not change anything

[Samarth_18 · Accepted solution]

Hi @JSher ,

Can you try to create a measure with below code and use it as filter on your table visual.

FilteredTable = if(max(Table2[Date2])>SelectedDate,1,0)

or 

FilteredTable = if(format(max(Table2[Date2]),"mm/dd/yyyy")>format(SelectedDate,"mm/dd/yyyy",1,0))

[JSher]

This worked!  Thank you

I also need to do an = with it so the format version worked for that.

As a note you have a bracket wrong, should be:

FilteredTable = if(format(max(Table2[Date2]),"mm/dd/yyyy")>format(SelectedDate,"mm/dd/yyyy"),1,0)