Dynamic change of Column Series in stacked column chart

[IFEDataPerson]

I am working on a stacked column chart visual where I want to dynamically change the column series. The column series data I want to use are separate columns. Anyone have any experience or suggestions? 

[AllisonKennedy]

Can you provide more detail on what your raw data looks like and any relationships? Why is the series in separate columns and what's the trigger for the dynamic change?

This sounds like something that could be done using Calculation Groups or parameters or similar, but not sure with the limited info you've provided so far.

[IFEDataPerson]

Hi @AllisonKennedy , 

I'm trying to use a slicer for the dynamic change. I've created a Dimension slicer Name "Category" which has a the dimension that I want to show on the graph in the Column series. I've not seen an example where the Column Series is dynamic so I was trying to write a SWITCH measure....but not sure if this even works.

I have two data tables Category and HRS which are currently unrelated. I tried to create an artificial join table by putting in a unique identifier in HRS table and relating it to the Category table but when I try and write a switch statement using RELATED, it doesn't see the relationship. I think the problem that the unique identifier I created is not unique and shows a many to many relationship istead of a one to many between the DimensionJoinTable and HRS table but I'm not sure. 

[AllisonKennedy]

@IFEDataPerson  It looks like you might be missing some values in your dimension table too? There shouldn't be any null/blank values in your dimension slicer.

You definitely want it to have unique values, so need to make sure you can get a truly UID. See Part 2 in this series for a bit of help on combining columns to make that happen if needed: https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power_92.html

And then, we'll need a peak at your SWITCH measure. 

Instead of doing a SWITCH, I'm wondering if you might want to UNPIVOT the columns in Power Query? Then you'll only have one column and it will create an Attribute column for you to use as the slicer.