Hide Columns based on heading not position

I have a user that wants the layout below with two dimensions on each axis and two measures.

The measure Sales Budget will only be populated for year 2021 green box and the other years orange box will always have nothing in them.

Is it possible to hide the Sales Budget based on looking at the dimension value 2021?

I believe I can't use position or end calculated lookup columns because if the user changes the criteria all the positions will change.



  • Hi John, 

    I just have the following idea. Create a calculated column for Sales Budget in 2021, make the measure Sales Budget invisible and reorder the calculated measure to the right position.


  • Hi Marlene,

    Good shout I'll give it a go but I think could be an issue when criteria changes and existing columns move. i.e. in image above location 003 closed so doesn't have any data/columns for year 2021 but they still want to see it as it had sales in years 2020 and 2019.

  • Maybe you could use absolute references in your calculated columns so you are sure to get 2021 and end the syntax with " ,0" to avoid problems if 2021 are missing?



  • Or maybe you could make a visibility agent of each location and year => hiding the column if sum of sales budget are 0?

    Agent syntax: sum(@"[003]";"[2020]", all, m2, 0) = 0.

    Remember to change action from hide value to hide column member. 

    But of course it depends how many locations and year you have got :-)

  • Thanks Louise,

    Yeah issue is it is 100 Locations and 3 Years :-(

    Problem is it's a finance request and they just want to data dump into Excel, arghh, but have set column order BUT also be able to select dimension members to change the data.

    I'm thinking I may just have to go with creating a new view of this data so the three sales years and one budget year are their own measures and if null/empty put 0, then create a new cube.

    I could then simply drag the columns in so always same order even if 0.




Please sign in to leave a comment.

Didn't find what you were looking for?

New post