Dynamic Column Header In a Matrix

[tom-lenzmeier]

Hello,

I have a matrix that has 6 value measures: Last 12 months sales/change, last 6 month sales/sales change, and last 3 month sales/change. I have been tasked with trying to figure out if I can add a slicer where the user selects either 12, 6, or 3 months. Ideally, we would have two values being used (sales & sales change) depending on the slicer value selected. Is there a way to have the column header change depending on the value selected? For example, if they chose the last 12 months, the matrix would show the sales for the last 12 months and the YoY change in sales. The header would say "L12M Sales" and "L12M Sales Chg.". Then if 6 is chosen the values naturally change but I'm hoping that the header changes too. 

I can get it to work for a single value, but then I get stuck.

[cengizhanarslan · Accepted solution]

You can do it cleanly with a Field parameter. Here’s a concrete example you can copy.

First, create your measures (you already have these, names just for example):

• [L12M Sales], [L12M Sales Chg]

• [L6M Sales], [L6M Sales Chg]

• [L3M Sales], [L3M Sales Chg]

1) Create the field parameter table

Modeling → New parameter → Fields (pick the 6 measures). Power BI will generate something like:

KPI Param =
{
    ("L12M Sales",      NAMEOF([L12M Sales]),      0),
    ("L12M Sales Chg",  NAMEOF([L12M Sales Chg]),  1),
    ("L6M Sales",       NAMEOF([L6M Sales]),       2),
    ("L6M Sales Chg",   NAMEOF([L6M Sales Chg]),   3),
    ("L3M Sales",       NAMEOF([L3M Sales]),       4),
    ("L3M Sales Chg",   NAMEOF([L3M Sales Chg]),   5)
}

Those text labels (“L12M Sales”, etc.) become your dynamic column headers.

2) Create a slicer for 12 / 6 / 3

Period Selector =
DATATABLE("Months", INTEGER, {{12},{6},{3}})

Put Period Selector[Months] on a slicer.

3) Filter the field parameter based on the slicer

Add this calculated column to the KPI Param table:

KPI Param[PeriodMonths] =
SWITCH(
    TRUE(),
    CONTAINSSTRING(KPI Param[Name], "L12M"), 12,
    CONTAINSSTRING(KPI Param[Name], "L6M"),  6,
    CONTAINSSTRING(KPI Param[Name], "L3M"),  3
)

Now create a relationship:

• Period Selector[Months] → KPI Param[PeriodMonths] (one-to-many)

4) Build the matrix

• Rows: whatever dimensions you need (Product, Customer, etc.)

• Columns: KPI Param (the field parameter)

• Values: KPI Param (Power BI handles this automatically when you drop the parameter)

Now when the user selects 12, they only see:

• L12M Sales

• L12M Sales Chg

…and the headers change automatically because the parameter labels are different.

That’s the simplest way to get two measures shown + dynamic headers driven by a 12/6/3 slicer.

[tom-lenzmeier]

I have 3 other line charts on the page with the matrix. Is it possible to get them to switch too? 

[tom-lenzmeier]

I found the answer to my own question. Thanks for your assistance.

[tom-lenzmeier]

Quick question. Would it be beneficial to use my calendar table for the integers? I could filter the slicer to only include 12, 6, and 3.

[cengizhanarslan]

I would prefer keeping it in a different table as you are going to use in for parameters. Keeping them in your calander table could break measures depending on your measures.

[danextian]

Hi @tom-lenzmeier 

Have you tried using field parameters and an extra column to the parameter table to be used in the slicer?

Current Year, Revenue, Transactions and Variance are the extra column in the parameter table above. In your case, you can use 12, 6, 3. The Parameter column values are what will appear in your visual.

[Ashish_Mathur]

Hi,

Headers cannot be dynamic.

[tom-lenzmeier]

One more thing, I know I can do it with bookmarks, but it would be cool if I could avoid that.