IOT Device Status (Data look up by ID)

[James_CA]

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  

This is the list of all device

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

[v-rzhou-msft]

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

[v-rzhou-msft · Accepted solution]

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

AllDeviceID:

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.

You 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. 

[amitchandak]

@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

[James_CA]

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

[James_CA]

Can anyone help please?

[James_CA]

@amitchandak

Can you please show me an example?