stacked column display does not change with slicer

[sonali_powerbi]

Hello, 

I have a slicer that I would like to control two charts: a stacked bar chart, and a clustered bar chart. I only want to display the latest 12 months data. The clustered bar chart behaves as expected, displaying only that data. 

The stacked bar chart though, displays only the data for the last 12 months, but keeps all values of months, with nothing displayed for months that do not match the slicer condition. 

I would like the stacked bar chart to match the display for the clustered bar chart, and not even display the older months, as they contain no information. I include a screenshot below. Can someone please help me figure this out?

Thanks!

[sonali_powerbi]

I think I missed a crucial piece of information - I am trying to control the two charts with a slicer from another table that is linked to the table used for making the charts. If I use a slicer from the same table, it works. 

I will need to use the other table though, as the YearMonth value in my data table is not unique, and I want to connect multiple tables. So I am trying to create a MasterYearMonth table, which will have unique YearMonth values, which can then connect to multiple tables. I want the slicer to be from the MasterYearMonth table, which is creating the messed up display explained above. 

So, I have table A:

Date      YearMonth     AValue

3/15/16  2016-03         10.3

3/24/16   2016-03        15.2

...

...

...

Note the YearMonth value is not unique. So if I have a similar table B, I cannot link YearMonth columns in the two tables, as it will be a many-many connection. This is the background reason why I want to link a slicer from a different table, but I haven't gotten as far as table B yet. 🙂 

Hence, I am creating a MasterYearMonth table, which only has each YearMonth value once. Then my Rolling12MonthFlag is defined in the MasterYearMonth table, which can control data from tables A, B, .. 

Both the plots shown above come from the same table, table A. If I have a slicer Rolling12MonthsFlag also defined in table A, it works as desired. If I define the slicer in the MasterYearMonth table, it does not seem to control the stacked bar chart properly. This is my issue. 

Here is a screenshot of how things work well with slicer from table A. I would like a display like this even when my slicer is from the MasterYearMonth table. 

Thanks so much to whoever is still reading. 🙂

[Anonymous · Accepted solution]

Hi sonali_powerbi,

>> So if I have a similar table B, I cannot link YearMonth columns in the two tables, as it will be a many-many connection.

I’d like to suggest you use CALENDAR function to add ‘CALENDAR’ table to link other tables which contains the data column.

Below is the Sample:

1. Build two tables to test.

‘DateAmount’

 

‘MasterYearMonth’

 

2. Add a calendar Table with date from above tables, and create relationships among those tables. (Since both tables contain the duplicate records, it’s not able to build a relationship.)

Dax:  CALENDARTable = CALENDAR(MIN(MIN(DateAmount[Date]),MIN(MasterYearMonth[Date])),MAX(MAX(DateAmount[Date]),MAX(MasterYearMonth[Date])))

 

Create the relationship:

 

3. Use Calendar table to filter the records.(Below is the visual’s struct and screenshots)

Stacked bar chart:

 

Clustered bar chart:

 

Slicer:

 

Use slicer to filter both table:

 

If above is not help, please provide more detial and feel free to let me know.

Regards,

Xiaoxin Sheng

[sonali_powerbi]

This works, Xiaoxin. Thank you so much.