Match multiple values is seperate table using ID and Date

[James_CA]

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 time on a single day on 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.

[v-deddai1-msft · Accepted solution]

Hello @James_CA ,

First, you must create a date table:

Dim_Date = CALENDARAUTO()

You can then use the following measure as a value in the viusal array:

Measure = VAR A = CALCULATE(MAX('Table'[device ID]),FILTER('Table','Table'[Date] = MAX(Dim_Date[Date]))) RETURN IF(ISBLANK(A),"False","True")

If this post helps, then consider Accept it as the solution to help other members find it faster.

Best regards

Dedmon Dai

[James_CA]

Thank you so much

[HotChilli]

You  now have a Fact table with a DeviceID dimension table and a Date dimension table (I think).

If you have the relationships set up correctly,

maybe a measure like this

MeasureD = VAR _NumRows = COUNTROWS(FactTable)
RETURN
    IF (_NumRows > 0, "TRUE", "FALSE")

then using a matrix visual with DeviceID (from dimension table) in Columns well and Date (from Date dimension) in Rows.

Let me know how it goes.