Forum Discussion

NSC7's avatar
NSC7
Icon for Helper I rankHelper I
4 years ago

Clustered bar chart - Filter table based on the bar selected

Hi,

 

I have a report which has a table visual , a clustered bar chart as below and few more visuals.

 

Table has data C ID, Category and Date Entered. In the clustered bar chart i am showing count of C ID based on Category for Current Year and Current Year + Month.

 

I want table data to filter based on the bar I select on the bar chart.

1. If i click on the bar for Category a2 and Current Year count , table should show data for a2 and where Date Entered year is 2022.

2. If i click on the bar for Category a2 and Current Month count , table should show data for a2 and where Date Entered year and month is Feb 2022.

 

To show the count in Bar chart I have created 2 measures as below

Current Year count = CALCULATE(COUNT(Sheet1[C ID]), FILTER(Sheet1, IF(YEAR(Sheet1[Date Entered])=YEAR(TODAY()),1,0) =1))
Current Month count = CALCULATE(COUNT(Sheet1[C ID]), FILTER(Sheet1, IF(YEAR(Sheet1[Date Entered])=YEAR(TODAY()) && MONTH(Sheet1[Date Entered])=MONTH(TODAY()) ,1,0)=1))

  

 

scenario 1 -Expected output - When i click on a2 , Current year count bar. This I am able to do when i put visual level filter on bar chart as YEAR(Sheet1[Date Entered])=YEAR(TODAY())

 

 

scenario 2- When i click on a2 , Current month count bar, it doesn't filter on year+month combination. 

 

I cannot put filter on the table visual as we have to show data for 2021 as well when nothing is selected.

How can I achive above mentioned both scenarios?

 

 

 

Need Help

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    Change your interaction type from cross-highlighting to cross-filtering.

    • NSC7's avatar
      NSC7
      Icon for Helper I rankHelper I
      4 years ago

      Cross filtering is on but it filters only on category a2.

      I want when I click on top bar "Current year count" of bar chart for category a2 it should filter data for category a2 and current year 2022.

      and when i click on bottom bar "Current month count" of bar chart for category a2 it should filter data for category a2 and current  year + month Feb 2022.

       

      Measures used in bar chart for counts are as below-

      Current Year count = CALCULATE(COUNT(Sheet1[C ID]), FILTER(Sheet1, IF(YEAR(Sheet1[Date Entered])=YEAR(TODAY()),1,0) =1))
      Current Month count = CALCULATE(COUNT(Sheet1[C ID]), FILTER(Sheet1, IF(YEAR(Sheet1[Date Entered])=YEAR(TODAY()) && MONTH(Sheet1[Date Entered])=MONTH(TODAY()) ,1,0)=1))
       
      Is there any other way of achieving it?
       
      Current results which is not correct, when i click on any bar it gets me every data in the table for category a2.
       
      Expected result should be, when i click on top bar rows marked in blue should come and when i click on bottom bar rows marked in red should show up in table.
       

       

       
  • sing5z's avatar
    sing5z
    Regular Visitor
    4 years ago

    Hello - Were you able to find a solution. I too have a simliar requirement and facing the same issue