Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
v-yingjl
Community Support
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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