PQ application parameter screening does not take effect

[Anonymous]

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!

[Anonymous]

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

[Anonymous]

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
    Selectrows

startdate 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.

[Anonymous]

IF M query like this , it will work.

let
    Source = Sql.Database("XXX.azuresynapse.XXX", "XXX"),
    ANYTABLE = Source{[Schema="XXX",Item="A"]}[Data],
    Selectrows = Table.SelectRows(ANYTABLE, each [dt] >= startdate and [dt] <= enddate)
in
    Selectrows

[v-yingjl]

Hi @Anonymous ,

Seems like could not reproduce it in my side as far as my test when I tried the same query like yours:

let
    Source = 
        Sql.Database(
            "xxx", "xxx", 
            [
                Query =
                "select TableA.* ,TableB.name #(lf)from TableA #(lf)left join TableB #(lf)on TableA.id = TableB.id"
            ]
        ),
    #"Filtered Rows" = Table.SelectRows(Source, each ([dt] >= Start and [dt] <= End))
in
    #"Filtered Rows"

The table would be filtered correctly:

Have you updated Power BI Desktop to the latest version to check it again?

Or you can try to use where statement in the query with parameters to check whether it works.

Note: With this way, the data type of parameters should be text not date.

let
    Source = 
        Sql.Database(
            "xxx", "xxx", 
            [
                Query = 
                "
                    select TableA.* ,TableB.name #(lf)
                    from TableA #(lf)
                    left join TableB #(lf)
                    on TableA.id = TableB.id
                    where [dt] >= ' "&startdate&" ' and [dt] <= ' "&enddate&" '
                "
            ]
        )
in
    Source

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

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