Filter on Click with Stacked Bar Chart with Year

[Anonymous]

How do I change the click action of a stacked bar chart to filter the data (similar to using a slicer) instead of opening a matrix of the data when using a time metric as the legend? I also found that using a time value in the chart and then switching back makes the cross filtering not work by clicking on the chart.

I am able to get the result I want by changing the Legend to a different, non time value, but I would like to have the stacked values be yearly values and to filter to a specific year when clicked on.

I can generate sample data and provide an example PBI file if my explanation is difficult to follow

[lbendlin]

I can generate sample data and provide an example PBI file if my explanation is difficult to follow

yes please?

[Anonymous]

Here's a link to the sample data csv file and the power bi file. Hopefully they work.

CSV, PBI 

EDIT: I found the issue, but it's really strange. Dragging the year field directly to the Legend makes clicking on the year filter to that year, but clicking the checkmark in the data tab, which adds the year to the X-axis, and then moving it to the Leged field makes clicking on the year show the raw data.

I also found that I have to remove all the fields and re-add them by dragging the data sources to the correct fields to convince it to filter instead of showing the raw data

[lbendlin]

Try not to use the auto date/time feature. It interferes with a lot of things and is generally not needed.

[Anonymous]

I'm not using auto date/time to my knowlege. I have a full date table with a manually created date hirearchy (year > month) which is used as the date field for the table I'm having issues with.

Date table DAX: 
Date = ADDCOLUMNS(
CALENDARAUTO(),
"Month", FORMAT([Date], "mmmm"),
"Year", YEAR([Date]),
"Day", DAY([Date]),
"Month Number", MONTH([Date]))

[lbendlin]

Let me extend that.  Try not to use any of the built in hierarchies. There's very seldom a need for them, especially when your visual doesn't use all the fields.

[Anonymous]

Are you saying to not use date hierarchies at all? I didn't use a built in hierarchy, I went into the model tab and created a hierarchy called "Date Hierarchy" with levels Year, Month.

If hierarchies don't work very well, why does the power bi training documentation suggest creating a date hirearchy?

[lbendlin · Accepted solution]

No idea.  Maybe it is easier for someone starting with Power BI. Or I just haven't found a truly good use case for them.  But nowadays even the standard slicer supports multiple fields which show as an ad-hoc hierarchy too. 

[Anonymous]

Thanks for the insight. I'll try not using hierarchies and just layer the hierarchy in the fields for a visual instead