Hi All. I'm very new to PowerBI and struggling to find the right way to achieve something.
The scenario is that we have a number of devices out in the field that should be sending messages in to our central system on a regular basis. So we need a report that shows all devices where we haven't had a message in for the past x number of days.
The raw data we have available looks something like this. The last column is a new coumn added through data transform to calculate the number of days from today back to the date of the message
| Device ID | Message Date and Time (UK) | Message Type | Days since Message |
| 1 | 12/12/2023 16:15:23 | 101 | 129 |
| 1 | 12/12/2023 15:01:56 | 101 | 129 |
| 1 | 03/11/2023 23:22:06 | 112 | 179 |
| 2 | 06/03/2024 09:23:00 | 100 | 55 |
| 3 | 04/03/2024 12:03:00 | 101 | 57 |
| 3 | 02/02/2024 18:06:25 | 100 | 88 |
| 4 | 06/02/2024 02:56:23 | 101 | 84 |
| 5 | 15/01/2024 15:03:24 | 100 | 106 |
For simplicity let's assume our cut-off days is 50 days which means all of the devices shown should be on the report. But in the case of devices 1 and 3 where we had multiple messages, all falling outsude the 50 day limit, we only want to see the most recent message - for Device 1 that would be 12/12/2023 16:15:23 and for Device 3 that would be 04/03/2024 12:03:00
I can't do anything to remove duplicate rows in the incoming table through data transformation as the table is used for other reports where everything needs to be available. So what I'm trying to achieve must be via the Report view.
There is a master table of devices with a unique row for each device ID so perhaps I need to be starting the report from that end but it's not clear how I would do that.
Is this possible to do and if so how.
many thanks for reading and all help gratefully received.
