Dynamic Column Headers in Power BI

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

There are several traditional methods to accomplish this 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 a lot 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 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 (SelectedYear) to the ‘Others (OM)’ field. The data mapping is shown in the below image.

3. Let’s change the position of the row grand total to bottom or you can switch it off if desired. Click on the ‘Totals’ dropdown in the ‘Home’ tab and select the relevant option.

4. We need to now insert a row to display the header. Select the first row and click ‘Calculated row’ from the ‘Insert row’ dropdown. To learn more about adding rows in Inforiver, watch this video.

5. The calculated row side panel opens on the right. Enter the title, provide any placeholder function or 0 or Blank in the Formula box and uncheck the ‘Include in Total’ option. Click ‘Create’.

6. The row gets inserted as shown below.

7. In the inserted row, click on the cell under the SalesAmount column to open the formula bar. In the formula bar, reference the Grand Row Total cell of the SelectedYear column.

8. You can see that the text gets displayed.

9. On changing the year, the value will change accordingly. Change the selection in the slicer.

10. From the Manage Columns dropdown, uncheck the SelectedYear column. This hides the column from the table. Though this column is hidden, the inserted row continues to show the value referenced in the grand total.

11. The first row (containing measure headers) can now be hidden. In the ‘Home’ tab, go to Display ->Misc. Disable the option ‘Show measure header’.

12. The header row can now be formatted as per your requirement.

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

Note that the column header will be shown only in the first page if there are multiple pages in the Inforiver report. We are working on enhancing the user experience in the upcoming version.

***

Example 2: 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.

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

2. Change the totals position and insert a calculated row as mentioned in steps 3 – 6 mentioned previously.

3. In the calculated row, reference the row grand total cells of the SelectedPeriod-1 and SelectedPeriod columns in the header row of prior period and current period columns.

4. Hide both the SelectedPeriod-1 and SelectedPeriod columns from the ‘Manage columns’ dropdown.

5. Hide the measure header and format the calculated row as desired.

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.