Use/ capture selected value from a filter and use it in a New column

[sharmisaranga09]

Hello,

I have been trying to show whether the device is online/offline based on it's last response time. I have created a new column with logic as, if the device's last response is within 2 hours then it's online and everything else is offline.

Here i want to make the 2 hours window as dynamic, user can pick between 1-24 hours and the logic should be 

if the device's last response is within <user selected> hours then it's online and everything else is offline.

if(device[devicelastresponsetime]>(UTCNOW()-TIME(selectedvalue),0,0)),"Online","Offline")

this returns blank as SELECTEDVALUE() is blank. Though as a measure this works perfectly

[sharmisaranga09]

I am able to get a measure working,

On/OffStatus = if(SELECTEDVALUE(device[lastresponsetime])>(UTCNOW()-TIME(SELECTEDVALUE(Parameter[Parameter]),0,0)),"Online","Offline")

Thank you.

[Anonymous]

Hi  @sharmisaranga09 ,

Does the above dax formula solve your problem, and if it doesn't meet your expected results, can you share sample data and sample output in tabular format? Or sample pbix after removing sensitive data. We can understand the problem better and help you.

Best Regards,

Liu Yang

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

[sharmisaranga09]

Hi, 

I couldn't attach pbix due to permission issue, Here is the sample I captured. DateTime is in UTC.

[sharmisaranga09]

Hi, Thank you.
I have multiple devices with single devicelastresponsetime value each, this gets updated whenever new response receives.
I still get all the values as offline eventhough I have chosen higher parameter value from your pbix report.
were you able to get the changes for selected value from slicer or have you used default value in parameter configuration?

[Anonymous]

Hi  @sharmisaranga09 ,

Here are the steps you can follow：

If you have more than one device, you can use the following dax.

Create measure

Group_date =
var _maxdate= MAXX(FILTER(ALL('device'),'device'[device_name]=MAX('device'[device_name])),[devicelastresponsetime])
var _hour=DATEDIFF(_maxdate,NOW(),HOUR)
return
IF(
    _hour=SELECTEDVALUE('Parameter'[Parameter]),"Online","Offline")

If you have a device, you can use the following dax.

Create measure

Single_date =
var _maxdate= MAXX(ALL('device'),[devicelastresponsetime])
var _hour=DATEDIFF(_maxdate,NOW(),HOUR)
return
IF(
    _hour=SELECTEDVALUE('Parameter'[Parameter]),"Online","Offline")

Best Regards,

Liu Yang

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