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

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

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