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.
Go to 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.
View solution in original post
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)
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.
Thank you so much
Check out the on demand sessions that are available now!
Check out the Winners!
Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.