cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IFEDataPerson
New Member

Dynamic change of Column Series in stacked column chart

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? 

3 REPLIES 3
AllisonKennedy
Super User
Super User

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.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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.

 

screen2.pngI 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. 

 

screen3.png

@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. 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.