cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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.

IOTDevice Reporting.JPG

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.

All Device ID.JPG

 

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.

Device Status.JPG

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

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")

Capture.PNG

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

Best regards

Dedmon Dai

View solution in original post

3 REPLIES 3
Highlighted
Super User II
Super User II

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.

Highlighted
Microsoft
Microsoft

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")

Capture.PNG

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

Best regards

Dedmon Dai

View solution in original post

Highlighted

Thank you so much

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors