Clustered bar chart - Filter table based on the bar selected

[NSC7]

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?

[sing5z]

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

[lbendlin]

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

[NSC7]

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.