Advanced options (what-if parameters, Power Query parameters, PBIDS files, and XMLA endpoints)

Power BI provides many advanced capacities. From tools for creating what-if analysis to dynamic DAX code, to storing data sources and structures for ease of use to serving advanced connections, to a myriad of data consumers using XML for Analysis (XMLA), including other non-Power BI tools, this portion of the chapter will dive deeper into these advanced capabilities.

What-if parameters

Power BI parameters allow for advanced analysis using multiple values for different scenarios. This capability is like What-If Analysis in Microsoft Excel. It allows for the creation of measures that calculate percentage value increases of existing numeric values. What-if parameters make it easy to see and use multiple percentage increases/decreases by automatically creating a slicer and a table with generated values. By creating a measure using the generated values of the what-if parameter, you can simulate changes to numeric data in a data model. For example, in our sales data, we can have aggregated total monthly sales. By using the what-if parameters capability, we can generate data used in a sales target of 100% to 200% and see the corresponding impact of that increase across the total monthly sales.

To use the what-if parameters, we can use the following steps:

1. Click the New Parameter button on the Modeling toolbar in Power BI Desktop.
2. Provide a name for the parameter. We’ll use Sales Increase.
3. Select a data type. For this example, we’ll select Fixed decimal number.
4. For Minimum and Maximum, select 1.00 and 2.00, respectively – to represent 100% and 200%.
5. Set the Increment value to the granularity desired in the slicer. If you want the slicer to move at 10% increments, then set this value to 0.10.
6. Set the Default value to be 1.00 as this will be 100%.
7. Lastly, leave the Add slicer to this page checkbox selected so the slicer will automatically be created on the current page.
8. Click OK.

In Figure 2.4, we can see how this what-if parameter can be configured:

Figure 2.4 – Example what-if parameter configuration

Once this has been completed, there will be a new table called Sales Increase and a slicer is added to the report. The slicer can be used as shown in Figure 2.5 to select a “what-if” value for the sales increase:

Figure 2.5 – Slicer created for the what-if parameter value

This table contains two fields: Sales Increase and Sales Increase Value. The Sales Increase field stores the generated increments from 1.00, 1.1, 1.2, 1.3, 1.4, and so on up to 2.0, while Sales Increase Value contains only the DAX expression using the SELECTEDVALUE function, which correlates to the location of the slicer. Together, these allow a dynamic multiplier to be selected using the slicer visual and another measure to be created that will multiply (or perform other math) against other fields in the data, like this:

= [Monthly Sales] * [Sales Increase Value]

This simple equation allows us to see what would happen if sales increase by nothing (100%) all the way up to double sales (200%) with this dynamic value set by the user using the slicer.