Forum Discussion

NHTKID's avatar
NHTKID
New Member
3 years ago
Solved

Relative Date/Time Filter on Power BI Visual

Hi,

 

I have a date/time column which is the timestamp when the devices checked in with the Intune.

I am trying to use the filter to display devices that haven't checked in for 24 hours.

 

But the Time/Date filter only give me the option to show devices checked in.

For example, when I select Filter Type = Relative time,

it give me option "Show items when the value is in the last 24 hours",

while I need it to be "Not in the last 24 hours".

 

Is there any smart and simple way to work around this?

 

Thanks!

 

Tips and Tricks
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi NHTKID ,

    Thanks for Waldo35 's solution, one more thing, we should use NOW function instead of TODAY which will return the time 00:00:00.

    If you want to use the relative time, here's the solution.

    Sample:

    1.Create a new table. Don't make relationship between the two tables.

    Table 2 = VALUES('Table'[Item])

    2.Create a measure.

    Check =
IF ( MAX ( 'Table 2'[Item] ) IN VALUES ( 'Table'[Item] ), 1, 0 )

    Put Item column from the new table in a visual, and put the measure in the visual filter then set its value to 0. After apply the relative slicer, get the correct result.

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

2 Replies

  • Waldo35's avatar
    Waldo35
    Helper I
    3 years ago

    Add a calculated column that checks for the hour mark, here i use 1 day, but this could also be done with hours of course 

     

    hour example:

     

     

    then in filter on this visual

     

     

     

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    Hi NHTKID ,

    Thanks for Waldo35 's solution, one more thing, we should use NOW function instead of TODAY which will return the time 00:00:00.

    If you want to use the relative time, here's the solution.

    Sample:

    1.Create a new table. Don't make relationship between the two tables.

    Table 2 = VALUES('Table'[Item])

    2.Create a measure.

    Check =
IF ( MAX ( 'Table 2'[Item] ) IN VALUES ( 'Table'[Item] ), 1, 0 )

    Put Item column from the new table in a visual, and put the measure in the visual filter then set its value to 0. After apply the relative slicer, get the correct result.

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

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