Forum Discussion

JSher's avatar
JSher
Frequent Visitor
5 years ago
Solved

Filter a table using a DateTime measure

Hello,

 

I am trying to filter a table based on a measure from a DateTime column.

 

My measure is: Measure = SELECTEDVALUE(Table[DateTimeColumn]) (creates a date/time mesure from another table)

 

I then try to filter my table using:

FilteredTable = FILTER(Table2,Table2[DateTimeColumn]<Measure))
 
This produces a blank table and I cannot figure out why.
 
I am filtereing on time. If my measure is 20210421 08:00:00
And my data in Table2 is:
20210421 06:00:00
20210421 07:00:00
20210421 08:00:00
20210421 09:00:00
 
I should be getting 0600 and 0700?
 
I have created a pbix to play with here
 
Thanks
General Comment
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi JSher ,

     

    You could create a measure and add it to visual filter, filter out the visual with measure=1.

    Measure = IF(SELECTEDVALUE(SecondTable[ActionDateTime])<SELECTEDVALUE(TimeAction[ActionDateTime]),1,0)

     

    Best Regards,

    Jay

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    JSher , You can not use slicer value while creating a new table, You can use that in a measure 

    measure =  countrows( FILTER(Table2,Table2[DateTimeColumn]<Measure)))

    • JSher's avatar
      JSher
      Frequent Visitor
      5 years ago

      Thanks for your reply.  

       

      measure =  countrows( FILTER(Table2,Table2[DateTimeColumn]<Measure))) Just give me the number of rows, I wish to display the information in those rows in a table or another format.

       

      Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi JSher ,

     

    You could create a measure and add it to visual filter, filter out the visual with measure=1.

    Measure = IF(SELECTEDVALUE(SecondTable[ActionDateTime])<SELECTEDVALUE(TimeAction[ActionDateTime]),1,0)

     

    Best Regards,

    Jay