Forum Discussion
Match multiple values is seperate table using ID and Date
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.
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
3 Replies
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
Thank you so much
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.
