Power Pivot Principles: Parameter Table

Welcome back to the Power Pivot Principles blog. This week, we are going to learn a way to introduce a parameter table into Power Pivot.

In a previous blog, we introduced the DAX function HASONEVALUE. This week we are going to use this function as a filter to create a parameter table. The parameter table is useful when we want to add a slicer to a PivotTable and modify the calculation dynamically with different dimensions or DAX expressions.

To use it, we must define a table that has no relationships with any other table (i.e. it is a disconnected table). The purpose of this table is to define a list of possible options for altering the calculations performed by one or more existing measures.

Let’s have a look at a simple example. Consider we have the following data table:

We create a parameter table with different FX rates. This table has no relationships with other tables, and usually has only one column, containing the parameter values. In our case, the table contains the description for Country and its corresponding FX Rate.

We add both tables to the Power Pivot data modelling interface and write the following measures. The first measure is the sum of total sales.

For the second measure, we use HASONEVALUE to filter out the FX rate chosen by the end user. To obtain the selected value, we use the VALUES function in the measure that uses the parameter. If the selection is not on one specific parameter, the measure returns default value of total sales. It should be noted that if the selection is multiple, which in most cases may be an invalid selection, the measure returns the default value as well.

Next, we export the measure and create a PivotTable based upon the filter Customer and insert a slicer based on the field Country in the parameter table.

The measure filters the value selected and calculates the result accordingly. At this stage, no specific value is selected in parameter table, the value returns from field ForeignCurrencySales remains the same as the field TotalSales. If we choose one value from the slicer (JPY for example), the result would be:

The field ForeignCurrencySales correctly calculated the value by using the FX rate listed in the parameter table (73.73). Thus, we can make the calculation dynamically with the selection on specific parameter in the slicer.

That’s it for this week!


Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.