need a dynamic column to display different values based on filter from an unrelated table

[mmahachi2]

sample pbix 

I have a request to show certain department values when the year 2022 is selected but a different set of department names when earlier years are selected. I know that switch and treatas can do this for measures but i need a column (i'm pretty sure) because I use these values as the rows in a matrix visual. 

I've tried various versions of switch and treatas is being used in the vlaues measures successfully but I can't get the actual department names to response to the date (year) selected in the slicer. I've uploaded an example pbix that is a simplified version of the model i'm working with. 

So when the year is set to 2021 I want to see the dept name in the 'department' column when the year is 2022 I want to see the dept names in the 'new dept list' column. 

I am trying to keep the department table with a distinct list of values b/c it serves as a dimensoin table to filter several tables in the model. I sure there's several ways to do this but I haven't been able to get it working and I would like to find the most efficient way and following best practices. 

[v-xiaotang]

Hi @mmahachi2 

Do you mean you want to create a dynamic column by the value selected in the slicer? i.e. when you select year=2021, then a column named 'department' will be created, then if you select year=2022, a column named 'new dept list' will be created.

If so, I am afraid that cannot be achievable, just as amaniramahi said. But you can use visual-level filter to limit values.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson]

What if more than one year is selected? What set of names do you use?

[mmahachi2]

they have to select only a single year

[amaniramahi]

I believe that calculated columns cannot be dynamic , I tried this before but nothing worked.

I believe this can be solved either with measures or work around. 

Elaborate please

[mmahachi2]

was afraid of that