Parameterizing Power BI Solutions

With the foundation of a Power BI deployment in place, components of the data retrieval and report design processes—as well as the user experience—can be parameterized to deliver greater flexibility for both technology services and users. For example, query parameters can isolate and restrict data sources to support changing source systems, templates can enable parameterized report development against pre-defined metadata, and M and DAX functions can deliver custom integration and analytical capabilities.

The recipes in this chapter cover both standard parameterization features and techniques in Power BI, as well as more advanced custom implementations. Examples of parameterizing data sources, queries, user-defined functions, and reports further express the power of the M language and its integration with other Power BI Desktop features. Additional examples, such as URL-based parameter filters, a dedicated forecasting or what-if...

Technical requirements

The following are required to complete the recipes in this chapter:

• Power BI Desktop
• SQL Server with the AdventureWorksDW2019 database installed. This database and the instructions for installing it are available here: http://bit.ly/2OVQfG7
• Files for this chapter can be downloaded from the following GitHub repository: https://github.com/gdeckler/PowerBICookbook2ndEdition

Filtering reports dynamically

In addition to the report filter options in Power BI Desktop covered in Chapter 4, Authoring Power BI Reports, filters can also be applied to published Power BI reports via the URL string. Rather than multiple, dedicated reports and report pages with distinct filter conditions, URL links with unique query strings can leverage a single published report in the Power BI service. Additionally, URL links can be embedded within a dataset such that a published report can expose links to other reports with a pre-defined filter condition.

In this recipe, two URL strings are created to demonstrate single and multiple filter parameter syntax. The second example creates a URL string for each row of the Product dimension table via an M query and exposes this dynamic link in a report visual.

Getting ready

To prepare for this recipe, follow these steps:

1. Download CH7_R1.pbix from the following GitHub repository: https://github.com/PacktPublishing...

Leveraging query parameters

Parameters are a primary component in building flexible, manageable query retrieval processes, as well as enabling simple filter selections. Hardcoded values in queries can be replaced with parameters, and a single parameter can be leveraged by multiple queries, thereby reducing development time and maintenance. Parameters are required to configure incremental data refresh policies on datasets and are commonly used to limit the volume of data loaded to a local instance of Power BI Desktop relative to the published Power BI dataset.

Parameters can also be useful during development in order to filter fact tables or large dimension tables to only a subset of data. Very large Power BI files can become slow and difficult to work with and thus the dataset author can add a filter via a parameter so that only a subset of rows are loaded locally. The dataset author can then simply revise the parameter such that all rows are loaded. This can be done just prior...

Working with templates

Power BI templates can be created from Power BI Desktop files as a means of providing users and other report authors with access to pre-defined metadata, such as M queries, DAX measures, model relationships, and report visualizations. As the template files do not contain actual data, they are very lightweight and, for import mode models, data is only retrieved when the template is opened. Additionally, if query parameters have been configured, a user interface is provided for entering parameter values, and these parameters can be integrated with the source queries and other components of the dataset.

In this recipe, a parameter and supporting query are added to a Power BI Desktop file to support the distribution of a Power BI template.

Getting ready

To prepare for this recipe, follow these steps:

1. Download CH7_R3_Start.pbix from the following GitHub repository: https://github.com/PacktPublishing/Microsoft-Power-BI-Cookbook-Second-Edition...

Converting static queries to dynamic functions

In addition to the standard library of functions available to M queries, user-defined functions can be created to encapsulate the logic of queries for dynamic application against parameter inputs. Like SQL-stored procedures, M functions can be created with or without input parameters, which can be required or optional. Additionally, as functions are values in the M language, just like table and list values, they can be invoked on demand and in multiple areas within a given Power BI data model.

In this recipe, a function is created to support the integration of a list of employee IDs maintained outside the data warehouse environment. The function accepts the employee ID values as parameter inputs and retrieves related column values.

Getting ready

To prepare for this recipe, follow these steps:

1. Download Employees.xlsx from the following GitHub repository: https://github.com/PacktPublishing/Microsoft-Power-BI-Cookbook...

Capturing user selections with parameter tables

An alternative method for providing parameter functionality to users of Power BI reports is via dedicated parameter tables. In this approach, the parameter values of a table are either computed during the dataset refresh process or are loaded as a one-time manual operation. DAX measures reference this parameter table and other tables and expressions of the model to enrich the self-service analysis experience and support Power BI report development. This approach is best suited for parameters that change infrequently since hard-coded parameter tables within Power BI means that the dataset owner must make such changes. If parameters need to change frequently, it may be better to have a database table or external file for the parameter table that is easier to edit and change.

The example in this recipe involves providing simple visibility to four alternative scenarios to the baseline annual sales plan—10 and 20 percent above...

Forecasting with what-if analysis

Power BI can be used to directly support the creation of forecasts, budgets, and other planned values of future business measures and events. The relationships and logic of these datasets, which are commonly implemented in Excel formulas and maintained by business teams, can be efficiently replicated within a dedicated Power BI Desktop file. Isolating the what-if input variables from the forecast creation, storage, and visualization in Power BI enables users to more easily create, analyze, and collaborate on business forecasts.

In this recipe, a Power Desktop model is used to ingest forecast-variable inputs from Excel, and process these variables with a dynamic transformation process to generate a forecast table available for visualization. This design enables business teams to rapidly iterate on forecasts, and ultimately supports an official or approved forecast or plan that could be integrated with other data models.

Getting ready

To...

Conclusion

The recipes in this chapter covered both standard parameterization features and techniques in Power BI as well as more advanced custom implementations. Examples of parameterizing data sources, queries, user-defined functions, and reports demonstrated the power of the M language and its integration with other Power BI Desktop features. Additional examples, such as URL-based parameter filters, a dedicated forecasting or what-if? tool, and user selection parameter tables, utilized both the transformation and analytical features of Power BI to empower users with greater control over the analysis and visualization of Power BI data models.