Forum Discussion

Dimitris_Kats's avatar
Dimitris_Kats
Icon for Helper V rankHelper V
2 years ago
Solved

Filter a bar chart based on selected matrix cell

Hi dear members.

I need your help.

I have a matrix with categories in the rows and multiple measures in the values.

For example in the rows I have:

Category 1

Category 2

Category 3

And in the columns:

Measure 1

Measure 2

Measure 3

I want to create a bar chart next to the matrix enabling users to select a specific cell(for example Measure 3, category 1) and display only that measure to the bar chart. Currently the bar chart display all measures for the selected row.

Any idea how to filter not only the rows but the .measure as well??

 

Thank you so much 

 

  • xifeng_L's avatar
    xifeng_L
    2 years ago

    Let's slightly modify the expression of the measure, pls try again.

     

     

     

    Use of Bar Chart = 
IF(COUNTROWS(ALLSELECTED(ColumnTable[Item]))>1,
    CALCULATE(
        [AutoIndicator],
        KEEPFILTERS('ColumnTable'[Item]="Measure1"),
        KEEPFILTERS('Fact'[Category]="Category1")
    ),
    [AutoIndicator]
)

     

11 Replies

  • xifeng_L's avatar
    xifeng_L
    Icon for Super User rankSuper User
    2 years ago

    Hi Dimitris_Kats ,

     

    Chart interaction can only pass filters for dimension fields to other objects. So, it is necessary to use the column labels of the matrix to achieve filter passing. 

     

    But if the column labels of the matrix are used, only one measure can be added, so this measure needs to be able to calculate different indicators based on different column label items.

     

    The Solution as follow:

     

    #1. Create an auxiliary table to use as a matrix column label. Such as:

     

    //New Table Expression

ColumnTable = 
DATATABLE(
    "Item",STRING,
    "Index",INTEGER,
    {
        {"Measure1",1},
        {"Measure2",2},
        {"Measure3",3}
    }
)

     

    #2. Create a dynamic measure that can calculate different indicators based on different column labels.

     

    //Measure Expression

AutoIndicator = 
VAR CurIndicator = SELECTEDVALUE('ColumnTable'[Item])
RETURN
SWITCH(
    CurIndicator,
    "Measure1",[Measure1],
    "Measure2",[Measure2],
    "Measure3",[Measure3]
)

     

    #3. Place the measure or column label fields created above into matrices and bar charts.

     

     

    #4. Edit interaction settings to filter.

     

     

    After completing the above settings, you can meet your needs. Such as:

     

    #Default

     

    # Selected

     

     

    Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

     

    Thank you~

     

     

     

  • Dimitris_Kats's avatar
    Dimitris_Kats
    Icon for Helper V rankHelper V
    2 years ago

    xifeng_L 

    The solution has worked perfectly. The only problem is that I can't use conditional formatting to the bar chart. I want to highlight the bar with max value but no matter what I try it's be overwritten 😞

    Any idea how to solve it?

    • xifeng_L's avatar
      xifeng_L
      Icon for Super User rankSuper User
      2 years ago

      Sorry, I have no idea about this. Because after using the legend field, it is not possible to continue with the conditional formatting feature.