Forum Discussion

sumneshnepalia's avatar
sumneshnepalia
Frequent Visitor
1 year ago
Solved

filter data on report refresh time

We have data in following format from Direct query and I need to filter a graph on last refresh time 

 

i.e. timestampvalue from "TIMESTAMP" column equal to timestampvalue from "Current time"
tried below logic but it doesnt work

Latest_timestamp_flag =
IF(LOOKUPVALUE(A[TIMESTAMP],A[TIMESTAMP],HOUR(A[TIMESTAMP]) & ":" & MINUTE(A[TIMESTAMP])) = HOUR(now()) & ":" & MINUTE(now()), 1, 0)



so if the current time is 8:04 AM then it should show only highlighted rows




Requirement is to show following table

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi sumneshnepalia ,

     

    This logic you're talking about holds true in the row context when creating calculated columns:

    And I created a measure, at this point the filter context works, you can also use SELECTEDVALUE instead of MAX:

    You can test the formula I've given and let me know if you have any problems, or clarify in your next reply if I've misunderstood what you've said.

     

    Best Regards,
    Zhu

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for the reply from lbendlin.

     

    Hi sumneshnepalia ,

     

    please try the following DAX:

     

    Measure=IF(HOUR(MAX('A'[TIMESTAMP]))&MINUTE(MAX('A'[TIMESTAMP]))=HOUR(NOW())&MINUTE(NOW()),1,0)

     

    The visual is then filtered using measure:

     

    Result:

     

    Best Regards,
    Zhu

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

    • sumneshnepalia's avatar
      sumneshnepalia
      Frequent Visitor
      1 year ago

      Anonymous  I think this logic will compare max timestamp for the date but I need the flag for current time stamp = time stamp of the date

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi sumneshnepalia ,

         

        This logic you're talking about holds true in the row context when creating calculated columns:

        And I created a measure, at this point the filter context works, you can also use SELECTEDVALUE instead of MAX:

        You can test the formula I've given and let me know if you have any problems, or clarify in your next reply if I've misunderstood what you've said.

         

        Best Regards,
        Zhu

         

        If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    what if you use a table visual and group by timestamp?

      • lbendlin's avatar
        lbendlin
        Super User
        1 year ago

        Try it out. Power BI will automatically group for you.

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    what if you use a table visual and group by timestamp?