Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
Add only the device ID and the Message Date to the table. Then change the Message Date aggregation to MAX, Then add a measure that calculates the Message Type for that ID and Date.
Add only the device ID and the Message Date to the table. Then change the Message Date aggregation to MAX, Then add a measure that calculates the Message Type for that ID and Date.
Hi. Thanks for the response. Message type is used for filtering out certain types of message.
With just device ID and Date in the table and using the Latest option on the date column, I do end up with a unique row per device. Discovering a few other things about the data that I didn't know so that's presenting some new challenges but at least this particular one is solved now. Thank you.