Forum Discussion
PQ application parameter screening does not take effect
hi, pbiers:
A very strange problem is that an SQL with join rotates very slowly in PQ. If sql statements without join, it will be very fast in PQ. Moreover, if sql statements with join has parameter filtering in PQ, parameter filtering will not work when the application is closed. PBI will load all data from sql database. Parameter filtering can be applied to tables directly if sql statement without join, that is import a table from sql database without any filters.
I have no access to alter sql view in sql database. The sql statement is ready. This sql table is very big , I want to refresh pbi on pbi service using dataset parameter set . therefore I can refresh all data online without consuming local ram.
I want to import several rows data with parameter filter then refresh all data on pbi service. But PQ query with sql statement can not load those data that parameter filters. it will load all data.
so, what I can do to solve this problem.
look forwad to your solutons.
thank you very much!
hi,v-yingjl :
thank you for your reply.
I retried it again just now.
I find that it doesn't seem to have anything to do with this(SQL statement with join).
M query with parameter screening is ok.
Loaded for some time after applying parameter filtering. I think of this load time as full load. It is my mistake. sorry.
so, there is no problem with M query with parameter screening and sql statement with join. It , that is sql with join, just takes a little longer time to load than with no join sql.
thanks a lot
5 Replies
First, if you already have a view, do you still need to use a SQL statement, as opposed to just the PQ functions? Nevertheless, if you have a parameter named FirstDate, which is the date value 6/1/2020, and your SQL is written like
[Query = "SELECT...WHERE [Date] >= "&FirstDate&" and [SomeSQLCOLUMN] = 123"]
You are saying that it won't filter?
--Nate
hi,watkinnc:
thank you for your reply.
My M query just like this:
let
Source =
Sql.Database("XXX.azuresynapse.XXX", "XXX",
[Query=
"select a*,b.name FROM A left join B on A.id= B.id",
HierarchicalNavigation=true])
Selectrows = Table.SelectRows(Source, each [dt] >= startdate and [dt] <= enddate)
in
Selectrowsstartdate and enddate are parameters.
This filter does not work. The pbid will load all data, rather than load data between startdate and enddate. very strange.
b.name will as primary key to connect other table in tabular model. the view I have no access to alter in sqldatabase. so this sql statement is needed.