Azure Percept to Stream Analytics to PowerBI DirectQuery connection with PowerQuery calendar

Charles Elwood 106 Reputation points MVP

Problem Definition : I have Azure Percept data with a timestamp being fed through Azure Stream Analytics to a PowerBI report. I want to add a custom calendar table and tie it together with a relationship but the datetime data type is not allowing the relationship to work correctly between the two tables in PowerBI desktop.

Possible solutions : is there a way to change data type for a field in a DirectQuery connection or is there a way to define the data type as date and create a separate field for time in stream analytics?

Steps to reproduce :

Start Azure Percept
Start Stream Analytics Job
Start PowerBI Desktop and use Get Data -> PowerBI Datasets to connect to the dataset in PowerBI service
Create calendar table in PowerQuery
Connect the two tables together with a relationship from date field in calendar table to datetimestamp field in powerBI service dataset.
Popup asks if you want to convert to DirectQuery connection, say yes and then connect the two date fields.
But in the report view a slicer on the calendar table date field does not filter the other tables that are a directconnection.
What I have tried :

Changed the format of the datetimestamp field from direct query to date format (no timestamp shown), but that doesn't work.
Reviewed documentation for stream analytics, to try and cast to a date data type, but there doesn't seem to be a date data type supported.
The query code (for Stream Analytics) that I use to place the Percept data into PowerBI.

GetArrayElement(Percept.ArrayValue.bbox, 0) AS bbox0,
GetArrayElement(Percept.ArrayValue.bbox, 1) AS bbox1,
GetArrayElement(Percept.ArrayValue.bbox, 2) AS bbox2,
GetArrayElement(Percept.ArrayValue.bbox, 3) AS bbox3,
CAST (udf.main(Percept.ArrayValue.timestamp) as Datetime) as DETECTION_TIMESTAMP,
"PowerBI Output"
"IoT HUB Input" as event
CROSS APPLY GetArrayElements(event.Neural_Network) AS Percept
CAST(Percept.ArrayValue.confidence as Float) > 0.6

@Amira Yousif @Jussi Niemela

Azure Percept
Azure Percept
A comprehensive Azure platform with added security for creating edge artificial intelligence solutions.
70 questions
Azure Stream Analytics
Azure Stream Analytics
An Azure real-time analytics service designed for mission-critical workloads.
282 questions
{count} votes

Accepted answer
  1. HimanshuSinha-msft 19,231 Reputation points Microsoft Employee

    Hello @Charles Elwood ,
    My apoloziges for the late reply on this . But i just tested the logic which a dummy data ( I do not have Percept knowledge or access )and it works for me . Not sure if this helps .


    select EventProcessedUtcTime , concat (DATEPART ( yyyy ,EventProcessedUtcTime),'-',DATEPART ( mm ,EventProcessedUtcTime),'-',DATEPART ( dd ,EventProcessedUtcTime)) as Date ,
    concat (DATEPART ( hh ,EventProcessedUtcTime) ,':',DATEPART ( mi ,EventProcessedUtcTime),':',DATEPART ( ss ,EventProcessedUtcTime) ) as Time
    from EHInput


0 additional answers

Sort by: Most helpful