Forum Discussion

JSher's avatar
JSher
Frequent Visitor
5 years ago
Solved

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

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

 

Need Help
  • Samarth_18's avatar
    Samarth_18
    5 years ago

    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))

     

6 Replies

    • JSher's avatar
      JSher
      Frequent Visitor
      5 years ago

      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's avatar
        Samarth_18
        Community Champion
        5 years ago

        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))

         

  • ERD's avatar
    ERD
    Community Champion
    5 years ago

    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's avatar
      JSher
      Frequent Visitor
      5 years ago

      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?