Dynamic Column Selection in a Table Visual

[mohit01chugh]

The objective is to implement a single, dynamic slicer that controls which specific columns from the main sales data table are displayed in an adjacent Table visual.

Input Data: A single sales data table containing all columns for Stock, Sale, and general details (Date, Items, Items_count, etc.).

Control Mechanism: A single slicer derived from a disconnected, helper table containing the selection values: "Stock," "Sale,"

Conditional Display Logic: The adjacent Table visual must display columns based on the slicer selection:

"Stock" Selected: Show only the two relevant Stock columns (e.g., Items and Items_count under the Stock header).

"Sale" Selected: Show only the two relevant Sale columns (e.g., Items and Items_count under the Sale header).

Nothing/Blank Selected: Show all available columns (e.g., Date, Stock Columns, and Sale Columns).

[v-sgandrathi]

Hi @mohit01chugh,

Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?

If you still have any questions or need more support, please feel free to let us know. 

We are more than happy to continue to help you.

[v-sgandrathi · Accepted solution]

Hi @mohit01chugh,

Thank you for sharing the screenshots. This behavior is expected, Power BI retains a column and its header in the visual even if all its cells are blank, so hiding values won’t remove the Sale header.

To address this, you have three options:

1.Use Field Parameters (recommended) by creating a parameter with Stock and Sale fields, adding it to your Table or Matrix, and controlling it with a slicer so only the selected field appears;

2.Unpivot the Stock and Sale columns in Power Query, then use the Attribute field in a Matrix and control which attributes display with a slicer; 3.Set up separate visuals for Stock-only, Sale-only, and both, then toggle between them using a slicer and bookmarks.

Field Parameters offer the best user experience, unpivoting is most model-friendly, and bookmarks are a straightforward alternative.

Thank you.

[v-sgandrathi]

Hi @mohit01chugh,

we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

[mohit01chugh]

Thanks for the follow-up. Apologies for the delayed response.
Refer to the Input data and Expected Output.

I tried with a solution, where "Stock" is selected:  Show the two relevant Stock columns, and the Sale relevant values are blank, that's okay, but the Sale header is still reflected in the table

[v-sgandrathi]

Hi @mohit01chugh,
Thank you @Ashish_Mathur @Anand24 for your answers.

As we have not received a response from you yet, I would like to confirm whether you have successfully resolved the issue or if you require further assistance.

Thank you for your cooperation. Have a great day.

[Ashish_Mathur]

Hi,

This Power Query code will transform the data into a neat table which you can now load into the Data Model

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dYu7CoAwDEV/RTor1Fpfo6KCs2PpIMXJ12D9f9OAMYvD4VxyiDFCxGLyp1vBYc7bEpaNjehmH/bol/16HbnzPvzPNTxJnaQqUVLlEBsgJWPOWW4BRcZcsOyAjIy5ZLkDNBlzxXIPfMZcszwABdnaBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    First2rows = Table.SplitAt(Source,2),
    Headings = List.Transform(Table.ToColumns(Table.FromRows(List.Transform(Table.ToRows(First2rows{0}), each Table.FillDown(Table.ReplaceValue(Table.FromColumns({_},{"Value"}),"",null,Replacer.ReplaceValue,{"Value"}),{"Value"})[Value]))), each Text.Combine(_,"/")),
    Custom1 = Table.FromColumns(Table.ToColumns(First2rows{1}),Headings),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Custom1, {"Date"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute.2]), "Attribute.2", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Date", type date}, {"Attribute.1", type text}, {"Items", type text}, {"Items count", Int64.Type}})
in
    #"Changed Type"

Hope this helps.

[Anand24]

@mohit01chugh,
What is the exact structure in which data is pulled? In your screenshot, Stock and Sale are merged columns which is not possible, hence asking about the exact structure of data.

Nevertheless, you can always use bookmarks but if data is structured correctly, you can simply a matric visual and use slicer.

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!! Proud To Be a Super User !!! LinkedIn

[mohit01chugh]

Thanks for the follow-up. Apologies for the delayed response.
Refer to the Input data and Expected Output.

I tried with a solution, where "Stock" is selected:  Show the two relevant Stock columns, and the Sale relevant values are blank, that's okay, but the Sale header is still reflected in the table