Filter chart based on slicer selection

[seankoh]

Hi all,

I have 5 tables which corresponds to 5 different locations (Site 1 - Site 5), each showing the number of cars, bikes and large vehicles on an hourly basis. These 5 tables are linked to another table called 'Location', via the LocationID.

I would like to have a chart which shows the number of cars, bikes and large vehicles for the different sites, depending on the value selected from the slicer, as shown below:

However, when I drag the values from all 5 tables into the chart, I end up with something like this:

As you can see, the size of the bars are shrinked to compensate for the other bars which represent the values from other tables, Also, the legend is now showing values from all the tables despite the slicer selection.

I want to filter the legend labels and bars based on the value selected in the slicer. Based on my knowledge, I know it is not possible and therefore I would like a workaround to this problem. Maybe a different method of modelling my data tables or something. Any help is kindly appreciated.

Thanks.

[Rfranca]

hi, @seankoh

This is because the columns are relacioands with the five tables.

Alternative to solve the problem:
1. Combine the five tables (with POWER QUERY) resulting in a consolidated table.

2. Create a measure that is the sum of the quantities of the five tables, in which case you should observe the related fields.

Please try and test using it. And please mark the right answer as answer if your issue has been resolved, otherwise, please feel free to ask if you have any other issue.
 
Best Regards,
Rfranca

[seankoh]

Hi @Rfranca,

1. If I combine the 5 tables into 1, how would I then select the different locations to display? Where would the values for my location slicer come from, since the data for all the different locations are now in one table?

2. Can you further elaborate on 'sum of the quantities of the five tables'?

Thanks.

Best regards,

Sean

[Rfranca · Accepted solution]

Hi, Sean

The combination of the five tables results in a NEW table similar to the existing ones. Instead of selecting information from the five tables, you will select and create your filters and measures from this consolidated table.

Do not forget that all five tables should have the same layout, otherwise it will not work correctly.

But notice this:
1. To consolidate each location table, you must have a column that identifies its location. If it does not exist you must create a column with this ID.

2. When you consolidate, use the option to create a new table, to facilitate understanding.

3. And after consolidating use this new table to create your controls and measures, and the filter also comes from this new table.

See the sequence of images ...

Please try and test using it. And please mark the right answer as answer if your issue has been resolved, otherwise, please feel free to ask if you have any other issue.

Best Regards,

Rfranca

[seankoh]

Hi Rfranca,

I tried your solution and it works brilliantly. Thank you so much for your help!

I also stumbled upon another solution by @v-huizhn-msft which doesn't require the tables to be consolidated.

(https://community.powerbi.com/t5/Desktop/Changing-column-graph-displayed-value/m-p/114452#M48262)

Best regards,

Sean