Hi,
A brief intro to my data set. I am monitoring the health of several devices. A message is sent to the database every 5 mins if the device is working, if it is not, a message is not sent.
In powerBI, I have 2 tables, 1 pulls in data from the past 30 days and the other pulls data from the past 1 hour. Based on the last hour message, I want to add a new column measure to table 1. I want to determined the value of this column based on this logic: if a message was seen from a device in the past 1 hour ( from table 2), then value of new column is healthy. If message was not seen from the device, I want to mark the last most instance of that device on table 1 as unhealthy -- sort of like a "last seen" concept, for when the nodes was last seen to be working.
So far, when I tried to do this, if a device was unhealthy, the DAX would be marking ALL of the past entries of this device as unhealthy. However, to make my dashboard useful, I want to preserve historic data of the devices so I can observe the changes over the 30 day period per device.
Another approach I tried, was to mark a device as healthy/unhealthy directly while pulling the past 1 hour data set from my DB, however, I was not able to use this table to mark the past 30 days table. Basically, I was hoping to implement this kind of logic: if device is marked unhealthy in past one hour, add "row" to past 30 days data, at time X and value in new column as unhealthy. Or another approach would be to mark its last most message as unhealthy, if adding a new row isn't possible
