parameterized direct query in powerBI

abhishek_ap4 · ‎03-02-2022

Hi All,

I'm trying to run a direct query with a parameter, (parameter value is selected based on user selection in slicer and capturing it in the parameter) below is the Mquery, The query works fine in PowerBI Desktop but when published into pPowerBI service get an error (unable to connect to DB/timeout error/contact DB adminstration) , their are other direct queries in file without where clause (slect * from table) this query is able to connect to DB only Query with where clause or parameter value is not able to connect on publishing into PowerBI service, any help would be ge greatly appriciated.

In below scenario I'm calling a function public.tna_get_po_milestone_date by parameter passing value (styleID) alomng with other hardcoded values.

let

//Create the query
dbQuery = "SELECT public.tna_get_po_milestone_date('" & styleID & "','ISD','31-dec-2022','FGA')",
//Get the data
Source = PostgreSQL.Database(<<Server Address>>, <<DB Name>>,[CommandTimeout=#duration(0, 1, 30, 0)]),

Test = Value.NativeQuery(Source,dbQuery, null, [EnableFolding=false])
in
Test

abhishek_ap4 · ‎03-05-2022

Hi @lbendlin ,

In databases supported by PowerBI Direct query its mentioned PostgreSQL is support, direct query with parameters for PostgreSQL works fine in my PowerBI desktop version, but issues is when publised to PowerBI serverice.

DB supported by PowerBI Direct Query : https://docs.microsoft.com/en-us/power-bi/connect-data/power-bi-data-sources

lbendlin · ‎03-05-2022

But you are looking for parameterized direct query, right?

abhishek_ap4 · ‎03-05-2022

actually we choose this direct query option becasue we were not able to plot calculated date field in a any visuals( mentioned issue in this link : https://community.powerbi.com/t5/Desktop/Data-visualization/m-p/2370040) , so we thought we get the calculation done at back end through direct query. any idea if we can plot caculated measure in any visuals ?