Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ShellyPC
Frequent Visitor

Dynamically hide table columns without column slicer

Hi

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:

ShellyPC_0-1685041275308.png

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:

ShellyPC_1-1685041372811.png

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.

Thanks

 

1 ACCEPTED SOLUTION
ShellyPC
Frequent Visitor

Hi - Just in case anyone has a similar problem. 

 

I left this project alone for 6 weeks and when I came back to it, the solution was actually very easy. 

Bottom line is that I could create a Field Parameter table from my list of measures.  However I wanted to use an existing slicer to slice the Parameter table and not do it directly.  An easy way to do this is to create a join table between the table my original slicer is working on and the new parameter table.  Then just create relationships from the new join table to the original table and the Parameter table.  This results in being able to use the original slicer to slice both tables.  I did need to mess around with the filtering direction as I had many to many relationships on both sides of the join table, But this works like a charm.

View solution in original post

4 REPLIES 4
ShellyPC
Frequent Visitor

Hi - Just in case anyone has a similar problem. 

 

I left this project alone for 6 weeks and when I came back to it, the solution was actually very easy. 

Bottom line is that I could create a Field Parameter table from my list of measures.  However I wanted to use an existing slicer to slice the Parameter table and not do it directly.  An easy way to do this is to create a join table between the table my original slicer is working on and the new parameter table.  Then just create relationships from the new join table to the original table and the Parameter table.  This results in being able to use the original slicer to slice both tables.  I did need to mess around with the filtering direction as I had many to many relationships on both sides of the join table, But this works like a charm.

amitchandak
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 %- https://youtu.be/qY1SDF1cwsg

Period Of Stay – Cohort Analysis: https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-3-Period-Of-Stay-Cohort-Anal...

 

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.

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: 

ShellyPC_0-1685133693196.png

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?

 

Thanks

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.