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 names in Power BI

by Inforiver | May 08, 2023 |

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.

dynamic column name based on slicer

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.

dynamic column name year slicer

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.

double click to change column name dynamically in powerbi
power bi dynamically update column name based on year

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

changing year column name will change dynamically in powerbi

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

table with dynamic column header based on measure filter

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.

dynamic column name based on current date powerbi

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

functions used to show previous or next period
functions used show previous or next period in powerbi

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.

dax-measures-to-achieve-slicer-selection-scenario

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. 

dax-measures-to-achieve-slicer-selection
  • Assign ‘SelPeriodSlicer Sales’ and ‘priorPeriodSales’ to ‘Values’ and ‘SelectedPeriod-1’ and ‘SelectedPeriod’ to ‘Others’.
dynamic-column-name-year-slicer
  • Reference the row grand total cells of the SelectedPeriod-1 and SelectedPeriod columns in the header of prior period and current period columns. 
dynamic-column-headers-power-bi-based-on-year
  • Hide both the SelectedPeriod-1 and SelectedPeriod columns from the ‘Manage columns’ dropdown. Format the measure header as needed. 
created-dynamic-column-headers-using-dax-measures-inforiver

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. 


Share this on:

Get Inforiver brochure

Maximize your business potential with Inforiver's paginated reporting, data entry, planning & budgeting capabilities
Download now
Inforiver
About Inforiver!
Inforiver is the fastest way to do everything in Power BI. It enables citizen developer productivity and unleashes true self-service with our intuitive and interactive no-code data app suite for Microsoft Power BI. The product is developed by Lumel Technologies Inc, who are #1 Power BI Visuals AppSource Partner serving over 3,000+ customers worldwide with their xViz, Inforiver, and ValQ offerings.
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram