Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Dynamically hide table columns without column slicer


I am trying to display a table that shows aggregated project costs in certain years in a projects life.  This is so that we can compare costs in the nth year of the life of those projects.

So for instance if we look at this table from Power BI:


The rows are for the past 5 years as well as this year so far (Start Year)  The columns show the costs in year 1 to year 6 of the age of the project.  (There are a number of different aggregations that may be required and the type of aggregation is simply chosen from a dropdown.  I have a measure for each year that is a simple switch statement that will return the required aggregation.)  I allow my users to choose how many years they want to go back so the above table is what they see if they choose 5 from a project age slicer.  If they were to choose 3 they would see the following table:


Looking at the year 2022 we see data for year 1 and year 2.  This is costs for 2022 and 2023.  Year 3 for 2022 is blank because that has not happened yet.  In the second table the columns for Year 5 and Year 6 are blank for each of the chosen start years.

In practice I allow users to go back 20 years so I actually have 20 measures (year 1 to year 20) for the aggregations.  I want to be able to remove blank columns dynamically.

I first tried using field parameter where I inserted all the above measures as fields. I left the “Add slicer checked”  This produced a table as well as a slicer for that table.  I took the created field from that table and put it into the columns for my table.  Now the slicer works perfectly and I can choose which columns I wish to see.  However I do not want my users to have to choose columns.  I feel there must be a way to use the already chosen age slicer to determine which columns to show.

The table created for the field parameters looks as follows:

YearsToShow = {

    ("Year 1", NAMEOF('Measures Table'[Year 1]), 0),

    ("Year 2", NAMEOF('Measures Table'[Year 2]), 1),

    ("Year 3", NAMEOF('Measures Table'[Year 3]), 2),

    ("Year 4", NAMEOF('Measures Table'[Year 4]), 3),

    ("Year 5", NAMEOF('Measures Table'[Year 5]), 4),

    ("Year 6", NAMEOF('Measures Table'[Year 6]), 5)



I looked at this and thought it would be simple to add a switch or if statement to this table so that I could use the selected value for “project age” to limit what table is returned.  However both switch and if only return scaler values and I want to return a table.  Is there some other Dax function that could dynamically return a table?  Or alternatively simply filter this table to ony return a subset of the table where the 3rd column of the above table (order) is less than or equal to “project age”.  I tried using filter here but could not get that to work.  I don’t know if this would even work as I doubt the table would change when the “project age” selection changes.

First prize would be to somehow dynamically change this table so that the columns in my table visual change accordingly.

The next thing I thought of doing is dynamically changing the values chosen for the slicer.  The slicer contains a value for each of the possible columns.  If I could programatically set the values in the slicer when the “project age” changes that may also work.  Then I could simply hide the slicer itself and have it change dynamically.  Again I could not figure out how to do this.

Either of these actions sounds simple – I just do not know how to do either of them or even if it would work.


Any help would be greatly appreciated.



Super User
Super User

@ShellyPC , I would like to see the base data.

I would have created something like this cohort

Power BI Cohort Analysis, Customer Retention %-

Period Of Stay – Cohort Analysis:


and then using Numeric parameter I can control number of years


If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

Thank you for looking at this. 

This is interesting and should work, I will try it today and either ask more questions  or let you know if I have success (and marking your solution as the accepted one). 


I need to take the time to understand your example properly then apply it to my data.  I will definitely do that.


I was trying to not use a matrix and rather use a simple table, as the measures i currently display here are all used in many other visuals.  The actual calculation of the values in the measures in my 20 "Year" columns have been tested and proved in other visuals and reports.  Also there are a bunch of other columns that need to go to the right of the age columns - I may have to move those into a seperate table.  My table currently displays correctly - I just end up with some blank columns that i was hoping to hide using the selected value from the existing Age slicer and not using a new slicer for the columns. 

Hi @amitchandak 

I took a try and ended up in exactly the same quandry here.  I end up with blank columns in my Matrix.  So I downloaded your Cohort Analysis from the above link, and added a slicer for your main date table (essentially that is what I am doing) to only show MonthYear column for 2019 and later.  Your solution also now has empty columns: 


Yes - it is easy enough to add another slicer for the "First Date" table to remove those columns but that is precisely what I do not want to do.  I want to use your selected value from the "date" slicer in order to dynamically set your "first date" slicer or somehow use that value to hide the blank columns in the Matrix.


Any ideas?



Helpful resources

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors