Filter Dinamically DirectQuery

[mmar]

Hi,

I have two tables, Premix_Batch and Historian, and both are DirectQueries from a SQL Database. Filtering manually (inside PBI) the orderNumber for Premix_Batch I get one row, among its columns there are StartTime and EndTime.

To get data for Historian I use a stored procedure that accepts as input a StartTime and a EndTime (something like: SELECT * FROM myHistorianTable WHERE LogDateTime BETWEEN StartTime2 AND EndTime2). At the moment I use prompted values for StartTime2 and EndTime2 (ex: BETWEEN '2020-03-22' AND '2020-03-24').

For reasons of data size I must filter the incoming data from myHistorianTable in the DirectQuery and I cannot import them.

I would like to be able to select all rows in Historian where the column value LogDateTime is between the values Start_Time and End_Time of the table Premix_Batch.

I have tried to use Parameters inside the DirectQuery but I am not able to modify the parameter dinamically.

Ex: "SELECT * FROM myHistorianTable WHERE LogDateTime BETWEEN "Parameter1" AND "Parameter2"".

I have also tried to use a query (that points at the first value in column StartTime1 from table Premix_Batch) inside the DirectQuery but I get the error: “Formula.Firewall: Query 'QueryName' (step 'StepName') references other queries or steps and so may not directly access a data source. Please rebuild this data combination.” 

Below the DirectQuery I use:

let
Source = Sql.Database("VHISTORIAN2017", "Runtime", [Query="SELECT * From (#(lf)SELECT * #(lf) FROM History#(lf)

WHERE History.TagName IN ('SysPerfConfigCPU')#(lf) AND wwRetrievalMode = 'Cyclic'#(lf)

AND wwCycleCount = 100#(lf) AND wwQualityRule = 'Extended'#(lf)

AND wwVersion = 'Latest'#(lf)

AND DateTime >= '"& Text.From(StartDate) &"' #(lf)

AND DateTime <= '"& Text.From(EndDate) &"' ) temp#(lf)LEFT JOIN AnalogTag ON AnalogTag.TagName =temp.TagName#(lf)LEFT JOIN EngineeringUnit ON AnalogTag.EUKey = EngineeringUnit.EUKey#(lf)LEFT JOIN QualityMap ON QualityMap.QualityDetail = temp.QualityDetail#(lf)

WHERE temp.StartDateTime >= '"& Text.From(StartDate) &"' "])
in
Source

Premix_Batch Table:

Historian Table:

[amitchandak]

Refer, if this can help

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dynamic-filter-on-direct-query-table-using-Da...

https://www.red-gate.com/simple-talk/sql/bi/power-bi-introduction-working-with-parameters-in-power-b...

[mmar]

Hi, 

I used from the beginning the red-gate article as reference to use the parameter. But then I cannot change dynamically the parameter setting it equal to some values in the Premix_Batch table.

Is there another way or a workaraound to achieve my goal?