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

Question

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.

SELECT
Percept.ArrayValue.label,
Percept.ArrayValue.confidence,
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,
Percept.ArrayValue.bbox,
CAST (udf.main(Percept.ArrayValue.timestamp) as Datetime) as DETECTION_TIMESTAMP,
Percept.ArrayValue.timestamp
INTO
"PowerBI Output"
FROM
"IoT HUB Input" as event
CROSS APPLY GetArrayElements(event.Neural_Network) AS Percept
WHERE
CAST(Percept.ArrayValue.confidence as Float) > 0.6

@Amira Yousif @Jussi Niemela

Answer 1

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 .

Query

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