topic Re: Use/ capture selected value from a filter and use it in a New column in Desktop

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

sharmisaranga09 — Mon, 26 Feb 2024 12:28:00 GMT

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

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

Anonymous — Tue, 27 Feb 2024 01:43:18 GMT

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

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

sharmisaranga09 — Tue, 27 Feb 2024 05:26:18 GMT

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?

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

Anonymous — Tue, 27 Feb 2024 05:15:13 GMT

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.

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

sharmisaranga09 — Tue, 27 Feb 2024 06:05:06 GMT

Hi,

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

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

sharmisaranga09 — Wed, 28 Feb 2024 06:09:18 GMT

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.