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

IOT Device Status (Data look up by ID)

Hi, I am new in PowerBI. I want to monitor some of my IoT Device by PowerBI Dashboard But I am unable to create the formula to DeviceID lookup on the date and create a new table

 

My Devices are generating this report  

IOTDevice Reporting.JPG

 

 

This is the list of all device

All Device ID.JPG

 

 

Device Status.JPG

I want to create a similar DeviceStatus table on PowerBI but I am unable to find any formula Can anyone tell me how can I do it? Which function should I use? It will be a great help, Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Hi @James_CA 

You need to build a calculated table ,add a calculated column and use measure to achieve your goal.

I build a table like yours to have a test.

Table (Add a calculated column and format the Data type as Date):

Date = 'Table'[Datetime]-TIMEVALUE('Table'[Datetime])

1.png

AllDeviceID:

2.png

Build a Date Table:

Date = 
GENERATE (
    CALENDAR ( DATE ( 2020, 09, 01 ), DATE ( 2020, 09, 04 ) ),
    AllDeviceID
)

 Measure:

Measure = 
VAR _Datevalue =
    CALCULATETABLE (
        VALUES ( 'Table'[DeviceID] ),
        FILTER (  'Table' , 'Table'[Date] = MAX ( 'Date'[Date] ) )
    )
RETURN
    IF ( MAX ( 'Date'[DeviceID] ) IN _Datevalue, "True", "False" )

Build a matrix visual to see the result.

3.pngYou can download the pbix file from this link: IOT Device Status (Data look up by ID)

 

Best Regards,

Rico Zhou

 

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

View solution in original post

6 REPLIES 6
Highlighted
Super User IV
Super User IV

@James_CA ,

Create a date column //prefer

Date = [Datetime].date //Prefer way is join it with Date table

 

Count occurrence=Count(Table[Date]) // or Count(Table[Datetime])

 

if (isblank([Count occurrence]) , "False","True")

 

Use it in matrix with Device Id and date



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

Hello,

 

I am new. Can you please explain a little more

 

Count occurrence=Count(Table[Date]) // or Count(Table[Datetime])

is giving me the total row count.

 

I am connecting two table by Date

Highlighted

Can anyone help please?

Highlighted

@amitchandak

Can you please show me an example?

Highlighted
Community Support
Community Support

Hi @James_CA 

You need to build a calculated table ,add a calculated column and use measure to achieve your goal.

I build a table like yours to have a test.

Table (Add a calculated column and format the Data type as Date):

Date = 'Table'[Datetime]-TIMEVALUE('Table'[Datetime])

1.png

AllDeviceID:

2.png

Build a Date Table:

Date = 
GENERATE (
    CALENDAR ( DATE ( 2020, 09, 01 ), DATE ( 2020, 09, 04 ) ),
    AllDeviceID
)

 Measure:

Measure = 
VAR _Datevalue =
    CALCULATETABLE (
        VALUES ( 'Table'[DeviceID] ),
        FILTER (  'Table' , 'Table'[Date] = MAX ( 'Date'[Date] ) )
    )
RETURN
    IF ( MAX ( 'Date'[DeviceID] ) IN _Datevalue, "True", "False" )

Build a matrix visual to see the result.

3.pngYou can download the pbix file from this link: IOT Device Status (Data look up by ID)

 

Best Regards,

Rico Zhou

 

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

View solution in original post

Highlighted
Community Support
Community Support

Hi @James_CA 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

 

Best Regards,

Rico Zhou

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