Forum Discussion

tmv3v's avatar
tmv3v
Frequent Visitor
6 years ago

Way of user passing parameters to Direct Query

Currently I have a problem where my datebase is very large and it takes a very long time to load and slice the data using slicers/filters

 

Is there a way for the user to input parameters into the query and then update the tables for quicker response?

 

For my dataset I have the following sample columns:

 

contractNumber, recordDate, transactionID

 

and I want to do something like:

 

SELECT * FROM database

WHERE

contractNumber = '"&contractNo&"' AND

recordDate BETWEEN '"&date_from&"' AND '"&date_to&"'

 

Thanks!

Need Help

3 Replies

  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi tmv3v ,

    "Is there a way for the user to input parameters into the query and then update the tables for quicker response?"

    In Power BI, there are two types of queries: M query & DAX query. they all can input parameters into the query when the original data is filtered, such as:

    M query:

    DAX query:

    (You also can edit M or DAX formula by yourself.)

     

    In fact, when using DirectQuery, the overall experience depends very much on the performance of the underlying data source

    About performance, you can refer to this artical:

    Using DirectQuery in Power BI 

     

    "and I want to do something like:..."
    If you want to realize the SQL code in Power BI, you can do it in "Edit Query":

     

    Best regards,
    Lionel Chen

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • tmv3v's avatar
      tmv3v
      Frequent Visitor
      6 years ago

      Thank you for your help. I think the methods you provided first extract all the data (for my database is about 80 million rows or data) and then apply filter to it. What I really want to do is to have a dynamic paramter inside the direct query in the WHERE clause as loading 80 million rows per refresh takes a while. 

       

      Secondly, does it mean that after publishing, users have no way to change parameters in the sql query?

       

      Thanks!

      • v-lionel-msft's avatar
        v-lionel-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi tmv3v ,

         

        Maybe you have two problems?

        One is the query performance problem, and the other is the dynamic parameter problem, right?

        About query performance problem, you should refer to the article I have given.

        About dynamic parameter problem, how about use slicers?

         

         

        After publishing, users still can change the parameters. You can try slicers and filters in Power BI Service.

         

        Best regards,
        Lionel Chen

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.