Create dynamic column to switch category or legend of charts

[Anonymous]

Hello,

Very new here and to the Power BI world. I've been trying to create a dynamic column selector that can be utilized to switch the "category" of data the chart will show.

For example:

• I have sales volume data (either in column or measure)
• I have these data in another table (called 'category') by different "categories": region, country, sales channel
• Also have another category in the same table as the sales volume data: quarter
• I want to create a multi-layer (2+ categories) bar graph that shows sales volumn by any category combination the user chooses to display (e.g., 1st layer region, 2nd layer country, 3rd layer quarter)

  
  I know how to make a switch for measures--switching between let's say sales volume and revenue data (using unrelated table), but having a hard time creating switch for the category/column

  What I tried is:
  

  • I created a new, unrelated table with 4 columns (layer 1 to 4) and each column containing the 4 categories, like so:
    
    
  • In my table (called 'Data') with sales volume data, I added 4 new columns with the following code except the layer #:
    
    Category Layer 1 =
    IF(HASONEVALUE('Layer'[Layer1]),
    SWITCH(VALUES('Layer'[Layer1]),
    "region", RELATED('category'[region]),
    "country", RELATED('category'[country]),
    "sales channel", RELATED('category'[sales channel]),
    "quarter", Data[quarter])
    ), "Only select 1")
    
    After doing this, I created 4 slicers for each category layers and I put in 'Category Layer 1' through 3 to the 'legend' or 'category' of the chart and put 'volume' into the 'value' section of the chart
    
    

     
    This setup is not working and each of the category layer is saying that "A table of multiple values was supplied where a single value was expected." I do see why this is not working, but can't figure out how to solve it. Could anyone help with a solution to what I am trying to do, please?

    
    

[danextian]

Hi @Anonymous 

I have created a post before that can address this:
https://community.powerbi.com/t5/Desktop/Dynamic-Category-Using-a-Slicer/td-p/216971

However, there are several drawbacks to this approach which I forgot to mention:

• May cause visual to load very slowly on  large dataset
• A legend color is unique to an item in dynamic category table. For example, Jan to Dec for name of month and Q1 to Q4  for the quarter will each have its own and the colors will be less visually pleasing the more items you have. You can change them manually but will be a tedious task if you have too many items. 

[venal]

@Anonymous 

Looks like there is no relationship in between the tables.

If any common column in between the tables, please create a relationship. So that you can get the expected data.

If this post helps, then please consider Accept it as the solution to help the other members find it more
If this post was helpful may I ask you to mark it as solution and click on thumb symbol?

[MFelix]

Hi @Anonymous ,

Check this post with a similar request to yours.

Check also the video below:

https://www.youtube.com/watch?v=VKkN-FF66Zw

Regards,

MFelix

[Anonymous]

Thank you @MFelix. I tried the reference method from the youtube link and it definitely works. However, this makes my file extremely large since I had to copy my huge database 3 times (more than 300mb now). Is there a more efficient way to do this?

@venalWhat did you mean by setting relationship? I am unable to create relationship because the 'category' title is the header, not the data, so no column to create relationship between my data table and category table.

[venal]

@Anonymous 

If possible, can you please share sample data and PBIX file to re-pro the issue from our end.

[Anonymous]

@venalI uploaded my pbix and database file in the links below. I trimmed the data due to size but these data are way bigger (200mb+). Thank you for your help in advance!

pbix:

https://we.tl/t-SzMdBGnKjA

database:

https://we.tl/t-rjx1JwixD1

@danextianThank you for the input. I tried this and looks like the issue you mentioned is making my dashboard inoperable..

[MFelix]

Hi @Anonymous

Although I agree with the options provided by the other users the main drawbacks that are present slow reports, increase in size etc makes (in my opinion) difficult to implement and have a good result.

Did you tried my approach of making 3 visual tables with the hierarchies you need and then using bookmarks make the visuals show/hide?

However with this you will make the levels be in the order you want and not on the selection of the user.

Regards
MFelix

[Anonymous]

@MFelixFor now, I will have to stick with your method of creating various visuals and use bookmark to hide/show the chart that the user selects. Still experimenting ways to make this extremely dynamic. Thanks