2

I am new to Power BI, doing some work. I have millions of records in SQL Server database, what i want to achieve is.. whenever my colleagues open the report on Power BI, it must ask for the date parameter. Once the date is submitted, then it must go to the SQL Server and fetch the data and then it must display the report.

Is it possible? If it is, could some one please show me how to achieve it?

Quentin
  • 45
  • 1
  • 5

1 Answers1

2

If you define a parameter in your report and use it for filtering data, when you load the report, the data matching the filter will be loaded. You can provide a default value, that you know will return no data (e.g. date in the future, if you are showing the data on or after some date).

You can declare a parameter (in your case of date data type) and use it to filter the data. To declare it, open your report in Power BI Desktop, go to Power Query Editor (by clicking on Edit Queries button in the ribbon) and define a new parameter in Manage Parameters dialog:

Manage Parameters dialog

Make sure the data type of the parameter matches the type of your data, i.e. if your data has time, define Date/Time parameter. If your data is Date, define Date data type parameter.

Then go to your query, which you want to filter and click on the button in the header of the column you which to filter. In Date/Time filters menu choose how to filter the data, e.g. to fetch the data that is on or after some date:

enter image description here

Then you can close Power Query Editor and return to the report. You can change the parameter's value from the drop down under Edit Queries - Edit Parameters.

If you want to avoid loading data for the default value of the parameter, you can save this report as template (File -> Export -> Power BI template), and then send this template to your colleagues. When they open it, they will be prompted to enter values for the defined parameters, and only after that, the report will connect to the data source and fetch the matching data. This means that first, you didn't use Power BI Online, but shared the report as a file, and second your colleagues has access to the data source to be able to load the data.

If you must publish the report to Power BI Online, then you can use completely different technique - instead of defining a parameter, pass the filter in the report's URL. When the report is loaded normally (e.g. you go to the workspace and open the report), it will show all the data (it will not be filtered). However, you can prepare a special URL for your colleagues, by appending ?filter=MyTable/DateColumn ge 2019-02-04 to the report's URL that you see in the browser's address bar. Replace MyTable with the name of the table you want to filter (e.g. SalesData) and DateColumn with the name of the field on which you want to filter (e.g. SalesDate). If there are spaces in these names, you must replace them with _0x0020_, but if possible, just avoid them. After that the filter defines an operator for filtering, e.g. ge means greater than or equals. And at the end is the value (the date in your case).

You can make a web page, where the user can select a date, and this page will generate the appropriate URL for your report. Or you can make another report, where the URL is constructed as a computed column or a measure based on some conditions (e.g. selecting a date from a slicer) and the user can click on the hyperlink, which will load the actual report, but filtered.

Andrey Nikolov
  • 12,967
  • 3
  • 20
  • 32
  • Thank you..but i want to load data in direct query with parameter on power bi web.. – Quentin Feb 05 '19 at 05:55
  • Changing parameter values in Power BI Online is inconvenient, also you can't avoid the initial data loading. Passing filters in the report's URL is the only way to fetch the relevant data from the start, and this works with DQ too, so you can use it. – Andrey Nikolov Feb 05 '19 at 06:57
  • Thank you.it's not possible to in web scenario by the parameter. – Quentin Feb 13 '19 at 16:37