Inforiver

Upcoming webinar on 'Inforiver Charts : The fastest way to deliver stories in Power BI', Aug 29th , Monday, 10.30 AM CST.    Register Now

Dynamic Column Headers in Power BI

by Inforiver | May 08, 2023 |

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.

Selected value function

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.

Selected year function

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.

Row grand total

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.

Calculated row column header

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’.

Placeholder function - Formula box

6. The row gets inserted as shown below.

Insert row in dynamic column header

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.

Click on the cell - Formula box

8. You can see that the text gets displayed.

Text display - Formula box

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

Year and value change

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.

Manage columns dropdown

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’.

Measuer header - column header

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

Format header row - column header

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.

Display selected period

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.

Display selected period - Measure

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

Assign Measures

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.

Reference - Row grand total

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

Manage columns - Column header

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

Calculated row - customize

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. 


Inforiver Logo
IBCS Certified
Power BI certified
About Inforiver!

Inforiver drives business productivity and business performance with faster time to insights inside Power BI through an intuitive no-code experience. The product is developed by Lumel Technologies (formerly Visual BI), which has a decade of experience in building add-on solutions on top of BI platforms (SAP BI and Power BI) with our suite of innovative products such as ValQ, xViz, and BI Hub
Headquarters
5920 Windhaven Pkwy, #130
Plano TX 75093
ValQ
xViz
BI Hub
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram