Forum Discussion

nithin_v's avatar
nithin_v
New Member
8 years ago
Solved

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)

 

 

  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    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] )
)



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

    relationshipexpected 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

1 Reply

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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] )
)



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

    relationshipexpected 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