Dynamic column filtering in a table with multiple headers

[Jcwinter]

Hi Community,

I’m working on a table visualization where I want to dynamically control the visibility of columns based on a filter selection. I was able to successfully implement dynamic filtering for a single header line (like in the image below):

However, my goal is to extend this logic to handle multiple headers, like this:

For example:

If the filter is set to X, I want columns 1A, 1B, 2A, and 2B to appear.
If the filter is set to Y, only columns 1A and 2A should be displayed.

It seems like managing multiple categories (e.g., "1" and "2") within the column headers isn’t straightforward. Is it even possible to achieve this functionality with Microsoft Fabric?

Any suggestions or insights would be greatly appreciated! Thank you for your help.

[v-achippa]

Hi @Jcwinter,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @MJParikh and @tayloramy for the prompt response. 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @Jcwinter,

We wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[tayloramy]

Hi @Jcwinter,

You can do this with a Matrix by driving the column hierarchy from a tiny disconnected “Columns” dimension and a slicer that maps a selected view (X or Y) to the allowed leaf columns. Power BI cannot literally toggle visual columns on/off with a measure, but if your measure returns BLANK() for disallowed columns and “Show items with no data” is off, those columns disappear. The trick for multi-row headers is to put two fields in the Matrix Columns well (Group and Letter).

1. Create a Columns dimension with these rows:
   ColKey, Group, Letter, Sort > 1A,1,A,1; 1B,1,B,2; 1C,1,C,3; 2A,2,A,4; 2B,2,B,5; 2C,2,C,6.
   Put Group and Letter into the Matrix Columns well to get the multi-row header.
   Reference: Matrix visual basics docs.
2. Create a View map table listing which columns belong to which view:
   • View X > {1A,1B,2A,2B}
   • View Y > {1A,2A}
3. Add a slicer on View (X,Y).
4. Use TREATAS in your measure to filter Columns based on the selected View so only the mapped leaf columns remain. Reference: TREATAS docs.
5. Turn off “Show items with no data” on the Matrix so BLANK columns are hidden.

That gives you dynamic multi-header columns: choose X to see 1A,1B,2A,2B; choose Y to see 1A,2A.

---

Deep dive (DAX you can paste)

Disconnected table: Columns

Columns =
DATATABLE(
  "ColKey", STRING,
  "Group", INTEGER,
  "Letter", STRING,
  "Sort", INTEGER,
  {
    {"1A", 1, "A", 1},
    {"1B", 1, "B", 2},
    {"1C", 1, "C", 3},
    {"2A", 2, "A", 4},
    {"2B", 2, "B", 5},
    {"2C", 2, "C", 6}
  }
)

Disconnected table: ViewMap

ViewMap =
DATATABLE(
  "View", STRING,
  "ColKey", STRING,
  {
    {"X","1A"},{"X","1B"},{"X","2A"},{"X","2B"},
    {"Y","1A"},{"Y","2A"}
  }
)

View = DISTINCT( SELECTCOLUMNS( ViewMap, "View", ViewMap[View] ) )

Sorting and hierarchy

• Sort Columns[Letter] by Columns[Sort] (optional).
• In the Matrix visual, put Columns[Group] then Columns[Letter] into Columns to get the two header rows.

Measure pattern
Assume your base measure is [Value] computed over your fact table and keyed by ColKey.

SelectedCols :=
VAR SelectedView =
    IF( HASONEVALUE( View[View] ), SELECTEDVALUE( View[View] ), BLANK() )
RETURN
    IF( NOT ISBLANK( SelectedView ),
        CALCULATETABLE( VALUES( ViewMap[ColKey] ), ViewMap[View] = SelectedView ),
        VALUES( Columns[ColKey] )  // no selection = show all
    )

Value Shown :=
VAR Allowed = [SelectedCols]
RETURN
CALCULATE(
    [Value],                             -- your real base measure
    TREATAS( Allowed, Columns[ColKey] )  -- filter the Columns dimension to allowed leaf columns
)

Visual setup

• Matrix Columns: Columns[Group], Columns[Letter]
• Matrix Values: [Value Shown]
• Matrix Rows: your row grouping
• Visual setting: Show items with no data = Off

Notes

• If your fact table does not have a ColKey like "1A", create it with a calculated column or derive it in the measure.

If you want, share a small PBIX via google drive/dropbox I can tailor the [Value] and ColKey pieces so it plugs straight into your model.

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[MJParikh]

You’re trying to dynamically hide/show grouped columns with multiple headers in a Fabric (Power BI) table or matrix. Right now you’ve managed single header filtering, but extending to multi-level headers like “1 → 1A,1B,1C” and “2 → 2A,2B,2C” is trickier. Here’s how you can approach it:

1. Model Your Data for Flexibility

Instead of storing each column as a separate field (1A, 1B, 1C, etc.), restructure into a normalized format:

• Columns: Category, SubCategory, Value.

• Example row: 1 | A | 123.
  This lets you control visibility with slicers, because headers are now data instead of hard-coded columns.

2. Use a Matrix with Field Parameters

Power BI supports Field Parameters, which allow end users to dynamically pick which columns/fields to display.
Steps:

1. Create a field parameter with all possible measures (1A, 1B, 1C, 2A, 2B, 2C).

2. Group them logically by categories (parameter table can include an extra “Category” column).

3. Add a slicer bound to this parameter so users filter which groups or sub-groups to show.

This way:

• If filter = X, you select 1A,1B,2A,2B in the slicer.

• If filter = Y, you only select 1A and 2A.

3. Build Hierarchical Headers

The Matrix visual automatically supports stepped layouts:

• Put “Category” (1,2) in Columns level 1.

• Put “SubCategory” (A,B,C) in Columns level 2.
  Now you get a structure like the screenshot, with expandable headers.

4. Apply Dynamic Logic

To automate X vs Y logic instead of manual slicer choice:

• Create a disconnected table for filter selection (X,Y).

• Write DAX to filter the field parameter table based on selection.
  Example:

VisibleColumns =
FILTER (
    'Field Parameters',
    ( SELECTEDVALUE(FilterTable[Filter]) = "X" &&
      'Field Parameters'[ColumnName] IN {"1A","1B","2A","2B"} )
    ||
    ( SELECTEDVALUE(FilterTable[Filter]) = "Y" &&
      'Field Parameters'[ColumnName] IN {"1A","2A"} )
)

Then bind VisibleColumns to the Matrix.

5. Limitations

• You cannot truly “hide” a column header dynamically in Power BI unless you use Field Parameters or measures. Otherwise, blank values still show.

• For Fabric, the recommended way is to restructure data and use Matrix + Parameters, not to manage static wide tables.