Microsoft PowerBI Match multiple values is seperate table using ID and Date

Question

I am making a demo IoT device monitoring dashboard. I can't understand which function should I use to check if devices are online on a certain date.

This is my sample report table1 from which I separate the Date to another column.

A device can report multiple times on a single day on the server. If it doesn't hit the server no report will generate.

Then I created a lookup table2 that contains all the device ID.

Now I created another table3 and generate a calendar date which I link with the table1 date.

Now In the column, I put my device ID and want to fill the column as true or false if the device reported on a particular date. I am unable to do it.

I used IF ( ISBLANK ( COUNTROWS ( RELATEDTABLE this function it didn't work

I want to create something like this. Which will look up the ID and date to report like it.

It will be a great help if anyone can share any idea.

Answer 1

The screen shots below give you everything you need.

There is a Device Connections table of the devices and when they connected. I have converted the DateTime to a date so that it can be joined to the Dates table, just a list of dates you want to check connections for. I have a relationship that connect the dates of the two tables.

Note: You could also preserve the time at which the device was seen if needed. It is probably best to have it as a separate column. I have discarded the time for simplicity.

I have created a single measure:

 HasBeenSeen = IF (CALCULATE(COUNTROWS(DeviceConnections)) > 0, TRUE, FALSE)

Which gives a TRUE/FALSE if the device has been seen or not for whatever context exists (e.g. a given date). You could also just count the number of occurrences and display them.

Then I created a matrix visual with the Date from the dates table on the rows, Device ID on the columns and HasBeenSeen as the values to give the desired result.