Forum Discussion

James_CA's avatar
James_CA
Frequent Visitor
6 years ago
Solved

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  

 

 

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

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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. 

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      James_CA
      Frequent Visitor
      6 years ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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