Dynamic column names in Power BI

Power BI currently has a limitation in dynamically updating column header names based on a filter. For example, I prefer to show values for a specific year (say, 2020), where the column name shows 2020 Sales. When I change the year filter to 2019, the column name must show 2019 Sales.

There are several traditional methods to change column headers dynamically in Power BI, such as the following:

1. Using bookmarks: Creating different copies of the table visual and using bookmarks. However, this gets complicated when the number of options in the slicer/filter are large.
2. Using DAX measures: Using DAX measures allows for customizations but this method is time- and effort-intensive.
3. Using field parameters: Measures can be used along with a calculation group for complex requirements but they are not suitable for casual Power BI users.

Example 1: Based on year slicer

This can be done easily in Inforiver Matrix for Power BI. Let us take the example where we have sales amounts by product category for 3 years. We would like to display the year selected in the slicer in the column header. This will require the following steps:

1. Create a DAX measure using the SELECTEDVALUE function to display the selected value of the slicer/filter. You can also append a text to the time period (if required) as shown below.

2. Add the required fields (ProductCategory and SalesAmount) to the Inforiver Matrix visual and add the DAX measure to the “Others (OM)” field. The data mapping is shown in the below image.

3. Double -click on the measure header name and reference the Grand Row Total cell of the SelectedYear(DAX Measure) to change the column name dynamically.

On changing the year, the column name will change dynamically as well:

4. Using the Manage Columns in the center of the Inforiver Toolbar, hide the SelectedYear Column.

You have now created a table with dynamic column header based on a measure filter.

Example 2: Based on current date 

You can also use the time functions provided by Inforiver to change the column name dynamically according to the current period.

The functions can be used to show previous period or next period as well: 

Example 3: Based on year/month and MTD/QTD/YTD slicers 

Complex scenarios can also be achieved in Inforiver using a bit of DAX. Let’s look at another scenario where we have year and month filters. We also have another filter which the user uses to indicate whether the report must show MTD/QTD/YTD values based on the month & year. Based on the selections, we want to display the selected period and prior period in the header.

We need to use four DAX measures to achieve this scenario – 2 measures for the current period sales and prior period sales and 2 measures for the selected period and prior period. 

SelPeriodSlicer Sales uses the Switch function to display Sales based on the MTD/QTD/YTD Slicer Selection for the selected Month/Year. Similarly, the prior period sales are calculated. 

SelectedPeriod shows the selected month/year. SelectedPeriod-1 shows the Prior Period based on MTD/QTD/YTD Slicer Selection for the selected Month/Year. 

The measures used are shown in the below image. 

• Assign ‘SelPeriodSlicer Sales’ and ‘priorPeriodSales’ to ‘Values’ and ‘SelectedPeriod-1’ and ‘SelectedPeriod’ to ‘Others’.

• Reference the row grand total cells of the SelectedPeriod-1 and SelectedPeriod columns in the header of prior period and current period columns. 

• Hide both the SelectedPeriod-1 and SelectedPeriod columns from the ‘Manage columns’ dropdown. Format the measure header as needed. 

You have now created dynamic column headers using DAX measures in Inforiver. 

***

Download a free edition of Inforiver Matrix and get started today!

We'd love to hear your ideas for Inforiver – be it a cool new feature or an improvement on existing features. Visit the Inforiver Community to share your ideas, vote on other submissions, and interact with other users.