How to Show/Hide Table Columns Dynamically in Power BI

In this guide, we will explore the process of dynamically showing or hiding table columns in Power BI. With this capability, you can enhance your data visualization and focus on the most relevant information. By following these steps, you will be able to customize your Power BI reports with dynamic column visibility.

Context:
The table presents data collected by stores for Total Sales recorded in each Month from January to December. If a Month has not been registered yet, all columns will display 0, which does not contribute useful information to the table. Our objective is to present a Table that only includes Months that have been recorded and contain valuable data without having to update the table columns manually for a new Month record added.

Step 1: Prepare your Power BI Report, begin by launching Power BI and opening the report you wish to modify. Ensure that the table containing the columns you want to dynamically show or hide is present and correctly configured as shown below;

Total Sales Recorded per Month

Step 2: To begin, navigate to the Modelling Menu and choose “New Parameter.” Next, select “Fields” and mark the checkboxes for all the columns you wish to hide or show in the table. As you select them, the chosen columns will appear in the pane on the left. Once you have made your selections, click on “Create” while ensuring that the “Add slicer to the page” checkbox is checked

A new table is created containing the columns added from the “New Parameter” Menu.

click this link on how to enable New Parameter on Power BI

Step 3: Next, you will need to add the column from the recently created Parameter to the Table by dragging it to the Columns on the Table viz. This action establishes a link between the table and the Parameter using the Months as a reference. As a result, you will notice that each column appears twice in the table, once from the original Table and once from the Parameter.

To proceed, delete the columns originating from the Table, retaining only the columns from the Parameter in the final table.

drag the parameter to the Table

What the Table columns should be like after removing the original columns

Step 4: The following step involves creating a control Table that dynamically generates a list of months up to the current month using a formula.

To start, you should create a new column that will store the current date using a formula

Current Date = Today()

Next, proceed to create a control Table with two columns. The first column should display the numerical representation of the months, while the second column should contain the names of the months. You can achieve this by using DAX formula.

MonthNum = GENERATESERIES(1,MONTH(MIN(PBI_Sales_Transactions_Dataset_Weekly[Current Date])),1)

MonthName = FORMAT(DATE(1, 'Control Table'[MonthNum], 1), "Mmmm")

Navigate to the “Model View” and you will find a newly created Control Table with columns named MonthName and MonthNum. Establish a link between the MonthName column in the Control Table and the Parameter Column from the Parameter Table. This will create a one-to-one (1:1) relationship between the two tables.

Linking the Parameter table to the Control Table from the Model view

Final Table with Columns that will Update Dynamically as the date changes

There are multiple approaches to creating the Control Table, allowing room for your creativity. The crucial aspect is to generate the control table dynamically using a formula.

If, after performing this step, the columns in your table do not reflect the desired changes, it indicates that the Control Table contains blank columns. In such a case, you can apply a filter to the table, specifically targeting the rows where the MonthName is not blank

Conclusion:

By following these steps, you have learned how to dynamically show or hide table columns in Power BI. This feature empowers you to customize your reports, improving data presentation and focusing on the most relevant insights. Experiment with different visibility formulas to create interactive and user-friendly visualizations in your Power BI reports.