Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nithin_v
New Member

Bringing in columns into bar chart dynamically by slicer selection

Hi,

 

I have data like this.

Category BrandManufacturerValue
C1 B1M110
C1 B2M220
C2B1M130
C2B2M240
C3B1M250

 

I have a dropdown with values "Brand","Category","Manufacturer".

When user selects Category, bar chart should be by Category and similar way for other. 

 

How to achieve this without changing any data format like unpivoting !

Solutions without bookmarks are preferred as my client is not very satisfied with that feature . ( I know its kind of strange)

 

category.PNGmanufacturer.PNGbrand.PNG

 

1 ACCEPTED SOLUTION
v-huizhn-msft
Employee
Employee

Hi @nithin_v,

You don't want to change the any data format, maybe you want to use the original table to create another table, you can create a new table to get expected table. 

First, I create a new table by clicking "New table" under Modeling on Home page, then type the formula below.

Table =
UNION (
    SELECTCOLUMNS ( Table1, "Level", Table1[Brand], "Value", Table1[Value] ),
    SELECTCOLUMNS ( Table1, "Level", Table1[Category ], "Value", Table1[Value] ),
    SELECTCOLUMNS ( Table1, "Level", Table1[Manufacturer], "Value", Table1[Value] )
)

1.PNG

Then type another table, and create relationship between them. Create a slicer including Level[Level], you will get expected result.

2.PNGrelationshiprelationshipexpected resultexpected result

In addition, there is no slicer based on column, so we have to put all the X-axis value in one column, otherwise we can't get result. And there are two similar threads for reference.

https://community.powerbi.com/t5/Desktop/Dynamic-Column-Based-on-Slicer-Selection/td-p/81126
https://community.powerbi.com/t5/Desktop/Dynamic-change-in-X-Axis/td-p/86167

Please download the .pbix file for detailed information.

Best Regards,
Angelia

View solution in original post

1 REPLY 1
v-huizhn-msft
Employee
Employee

Hi @nithin_v,

You don't want to change the any data format, maybe you want to use the original table to create another table, you can create a new table to get expected table. 

First, I create a new table by clicking "New table" under Modeling on Home page, then type the formula below.

Table =
UNION (
    SELECTCOLUMNS ( Table1, "Level", Table1[Brand], "Value", Table1[Value] ),
    SELECTCOLUMNS ( Table1, "Level", Table1[Category ], "Value", Table1[Value] ),
    SELECTCOLUMNS ( Table1, "Level", Table1[Manufacturer], "Value", Table1[Value] )
)

1.PNG

Then type another table, and create relationship between them. Create a slicer including Level[Level], you will get expected result.

2.PNGrelationshiprelationshipexpected resultexpected result

In addition, there is no slicer based on column, so we have to put all the X-axis value in one column, otherwise we can't get result. And there are two similar threads for reference.

https://community.powerbi.com/t5/Desktop/Dynamic-Column-Based-on-Slicer-Selection/td-p/81126
https://community.powerbi.com/t5/Desktop/Dynamic-change-in-X-Axis/td-p/86167

Please download the .pbix file for detailed information.

Best Regards,
Angelia

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.