• Home
  • About Us
  • Contact Us
  • Privacy Policy
  • Special Offers
Business Intelligence Info
  • Business Intelligence
    • BI News and Info
    • Big Data
    • Mobile and Cloud
    • Self-Service BI
  • CRM
    • CRM News and Info
    • InfusionSoft
    • Microsoft Dynamics CRM
    • NetSuite
    • OnContact
    • Salesforce
    • Workbooks
  • Data Mining
    • Pentaho
    • Sisense
    • Tableau
    • TIBCO Spotfire
  • Data Warehousing
    • DWH News and Info
    • IBM DB2
    • Microsoft SQL Server
    • Oracle
    • Teradata
  • Predictive Analytics
    • FICO
    • KNIME
    • Mathematica
    • Matlab
    • Minitab
    • RapidMiner
    • Revolution
    • SAP
    • SAS/SPSS
  • Humor

Deep Dive into Query Parameters and Power BI Templates

May 15, 2016   Self-Service BI

Two of the new features in the Power BI Desktop April Update are Query Parameters and Power BI Template files. In this blog post, we will take a deeper look at the new capabilities and scenarios that these two features enable in Power BI.

At a very high level, Query Parameters allow users to easily make parts of their reports and data models (such as a query filter, a data source reference, a measure definition, etc.) depend on one or more parameter values. On top of this capability, Templates allow users to export the definition of a report (report + data model + queries definition + parameters, if any) without including the actual data. Users can easily instantiate a Template using Power BI Desktop, which will ask them for parameter values (if needed) and create a new Power BI Desktop Report (PBIX file) based upon the contents in the Template file (PBIT file).

Let’s take a closer look at how this can be done…

Query Parameters

With the new Query Parameters feature, users can now easily define one or multiple parameters to be used in their queries, Data Model and report layers in Power BI Desktop. Users can define new parameters by using the “Manage Parameters” dialog in the Query Editor window.

0d54a097 2bd1 403c 809e 726ea994a082 Deep Dive into Query Parameters and Power BI Templates

From this dialog, users can create new parameters and specify metadata and settings for each parameter:

  • Parameter Name.
  • Parameter Description: This will be displayed next to the parameter name in downstream experiences, and helps the user who is specifying the parameter value to better understand the purpose and semantics of this parameter.
  • Optional vs. Required: Users can specify whether a certain parameter is optional or a value for that parameter must be specified (required).
  • Parameter Type: This field applies a Data Type restriction on the input value for the parameter. For instance, users can define a parameter of type Text, or Date/Time. Users can also specify Any type for more flexibility.
  • Parameter Accepted Values: In addition to Data Type restrictions, users can apply further restrictions to the allowed values for a given parameter. For instance, users could specify that the Data Type for a parameter is Text and restrict the acceptable values to a static list of Text values. Users will then be able to pick one of these values when specifying the parameter value to use.  Currently, only a static list of Accepted Values (or any value of the expected type) are accepted. In future updates, we will enable users to bind the list of Accepted Values for a parameter to the output of another query. This would enable dynamic sets of options to be displayed to the user, maybe even based on their selection for a previous parameter. A typical example for this would be making a “City” parameter change the list of values to select from, based on another parameter that allows users to specify a “State”. Also in a future update, we will enable users to select more than one value from the list of Accepted Values, rather than just one as it is the case in the current product version.
  • Default Value: This setting allows the Parameter creator to specify what the default value or selection should be for the user specifying the parameter value.
  • Current Value: This setting allows users to specify the value for this parameter in the current report.

e935f3ae 6dee 46b5 9702 bbbf0bb6d503 Deep Dive into Query Parameters and Power BI Templates

After defining one or more parameters and clicking OK in the “Manage Parameters” dialog, users will get back to the Query Editor dialog and will see a new query defined for each parameter. Note that Parameters are just like any other query in Power BI Desktop; they can be referenced from other queries, loaded to the Data Model and reference in DAX expressions, etc. By default though, Parameters are not loaded to the Data Model, so users have to right-click on a parameter and select “Enable Load” if they want to have them loaded to the Data Model.

f140930f a3fd 4250 8da4 891b11cd0cc8 Deep Dive into Query Parameters and Power BI Templates

Now that users have defined one or more parameters, they need to specify how they are being referenced or used by other queries. We’re adding support via the most common UX dialogs to accomplish this. Currently, most of the Data Source dialogs support referencing parameters on each input field. Once users have parameters in a Power BI Desktop report, and they access a data source dialog, they will see a new widget that allows them to switch the input mode for a given input box. The two supported modes are “Enter a value” (i.e. a static value) or “Parameter” (to reference an existing parameter from the PBIX file).

efcda142 bcc2 4730 bafa 7d9a10fd43d0 Deep Dive into Query Parameters and Power BI Templates

In addition to Data Source dialogs, parameters can also be referenced from the “Filter Rows” dialog and the “Replace Values” dialog. In future updates, we will add support for referencing parameters from other dialogs, such as the “Insert Conditional Column” dialog.

e7640ff2 dcd1 45ff 828f 305070769fc6 Deep Dive into Query Parameters and Power BI Templates

61e00325 b38d 4923 9383 ec228108b59d Deep Dive into Query Parameters and Power BI Templates

After Query Parameters have been referenced from other queries as needed, users can hit “Close & Apply” in the Query Editor ribbon to get their data and parameters loaded into the data model. At that point, they can start creating the report and even reference these parameters from DAX expressions, such as the one in the following screenshot.

a466a39b 6ef8 46a6 9c17 06673fc8a18e Deep Dive into Query Parameters and Power BI Templates

Within the Report view, users can edit parameter values by using the “Edit Parameters” button in the Home ribbon tab (under “Edit Queries”).

37f9f13d 1a1a 445f 9fd0 4c152e95d876 Deep Dive into Query Parameters and Power BI Templates

This will bring up a dialog to allow users to reconfigure parameter values and will refresh the data and report visuals based on the new parameter values.

1caa4375 f930 4e89 980a bbb8bb44c478 Deep Dive into Query Parameters and Power BI Templates

Note that currently, parameter values can only be modified within Power BI Desktop. We’re actively working on ways to allow users to change parameter values within PowerBI.com for published reports consumed via the Web experience.

To summarize, Query Parameters allow users to:

  • Define one or more parameters and associated metadata (name, description, etc.)
  • Apply restrictions to the values that a parameter can have, including a Data Type as well as the ability to provide a finite list of values accepted for that parameter. In the future, we will also provide the ability for that “list of values” to be dynamically calculated. We will also allow users to select more than one value from that list, as opposed to today’s single value selection.
  • Query Parameters can be referenced via the UX dialogs for most common operations in Power BI Desktop’s “Get Data” & “Query Editor” experiences: Data Source connection dialogs, Filter Rows and Replace Values. In the future, we will add support for referencing parameters from more dialogs. As a temporary workaround, you can reference parameters via the M code for any step in your queries.

Templates

Another new feature in the Power BI Desktop April Update that we will cover in this blog post is Templates. This feature allows users to export a Power BI Desktop report as a template (PBIT file), which can be instantiated as a new Power BI Desktop report (PBIX file).

A Power BI Report Template contains the definition of the Report (pages, visuals, etc.), Data Model definition (schema, relationships, measures, etc.) and Queries definition (collection of queries, including Query Parameters, etc.). In other words, a Power BI Report template includes pretty much everything that a Report file includes, with the exception of the data itself. When a user tries to instantiate a template, either via double-click on the PBIT file, or by using the “File –> Import –> Power BI Template” path, a new Power BI Desktop file will be created, containing the actual data based on current user’s credentials for data sources, etc.

In order to Export a Power BI Template, users can click “File –> Export –> Power BI Template” from the main Power BI Desktop window.

66eac46f ee27 4033 86f2 040657bda4c5 Deep Dive into Query Parameters and Power BI Templates

This will bring up a new dialog where users can provide a description for the template, followed by a Save File dialog where they can pick the name and path to save the template file to.

48f34e27 4d50 4275 81b2 79369fee56fb Deep Dive into Query Parameters and Power BI Templates

261937f6 255a 4eef b335 c16038a193f4 Deep Dive into Query Parameters and Power BI Templates

Notice how the file size of a Power BI Template (PBIT file) is much smaller than the size of a Power BI Report (PBIX file). Well, in this case, not that much since my dataset size was small anyways… : )

8382ad02 4b21 4be3 b2e9 4e73949c3d54 Deep Dive into Query Parameters and Power BI Templates

Now that users have created a Power BI Report Template, they can use it to instantiate new Power BI Reports on their machine, or share the template file with other users to let them create new Power BI Reports based on this template. There are two ways in which users can consume a Power BI Report template:

  • By double-clicking on the PBIT file in a machine with Power BI Desktop installed. This will launch Power BI Desktop and get them into the “Import Template” flow.
  • Within Power BI Desktop, clicking on “File –> Import –> Power BI Template”. This will create a new Power BI Desktop file based upon the contents defined in the template.

23953a3a 4f67 4859 9934 b445622c7d68 Deep Dive into Query Parameters and Power BI Templates

As part of importing a template, users will be asked to provide values for parameters defined in the template. For instance, importing the “Customers by Country” template that we created in the previous steps will prompt users to select a Country value from the list of accepted values that we specified while defining the parameter.

22464950 de87 46d9 b46b afca079d806c Deep Dive into Query Parameters and Power BI Templates

Once Parameter values have been specified, a new PBIX file will be created, containing all Report pages, visuals, data model artifacts and queries as the original PBIX file, but containing the data based on the current user’s credentials and parameter selection.

0ec2a60f 531a 4ac7 80d6 3171482c6763 Deep Dive into Query Parameters and Power BI Templates

Do you find Query Parameters and Power BI Report Templates valuable? If so, we encourage you to try this feature using the latest Power BI Desktop version and share feedback with us via our usual channels (Smile/Frown, Community Forums, UserVoice Feature Suggestions, etc.). You can also download the Power BI Report Template that I created in this blog post to explore the contents in more detail.

Let’s block ads! (Why?)

Microsoft Power BI Blog | Microsoft Power BI

deep, Dive, into, Parameters, Power, query, Templates
  • Recent Posts

    • Dapper Duo
    • AI Weekly: These researchers are improving AI’s ability to understand different accents
    • Why Choose RapidMiner for Your Data Science & Machine Learning Software?
    • How to Use CRM Integration to Your Advantage – Real World Examples
    • WATCH: ‘Coming 2 America’ Movie Review Available On Amazon Prime & Amazon
  • Categories

  • Archives

    • March 2021
    • February 2021
    • January 2021
    • December 2020
    • November 2020
    • October 2020
    • September 2020
    • August 2020
    • July 2020
    • June 2020
    • May 2020
    • April 2020
    • March 2020
    • February 2020
    • January 2020
    • December 2019
    • November 2019
    • October 2019
    • September 2019
    • August 2019
    • July 2019
    • June 2019
    • May 2019
    • April 2019
    • March 2019
    • February 2019
    • January 2019
    • December 2018
    • November 2018
    • October 2018
    • September 2018
    • August 2018
    • July 2018
    • June 2018
    • May 2018
    • April 2018
    • March 2018
    • February 2018
    • January 2018
    • December 2017
    • November 2017
    • October 2017
    • September 2017
    • August 2017
    • July 2017
    • June 2017
    • May 2017
    • April 2017
    • March 2017
    • February 2017
    • January 2017
    • December 2016
    • November 2016
    • October 2016
    • September 2016
    • August 2016
    • July 2016
    • June 2016
    • May 2016
    • April 2016
    • March 2016
    • February 2016
    • January 2016
    • December 2015
    • November 2015
    • October 2015
    • September 2015
    • August 2015
    • July 2015
    • June 2015
    • May 2015
    • April 2015
    • March 2015
    • February 2015
    • January 2015
    • December 2014
    • November 2014
© 2021 Business Intelligence Info
Power BI Training | G Com Solutions Limited