I have a project for which I am creating a dashboard. In this dashboard I have multiple pivotcharts that use slicers connected to all pivotcharts to filter and visualize data. This works great when all the pivottables come from the same table of data, but not when introducing additional pivottables based on different source tables.
Heres a simplified example:
Let say I have Table1 with two columns - ID and age. For each ID there is only one entry of age.
And I also have another Table2 woth two columns -ID and hobby. For each ID I can have multiple (unknown) number of hobbies.
I have loaded both tables into Data Model and established a 1 to Many relationship between the id columns in both tables.
Now I want to create a pivotchart from Table1 to display age distribution and create a pivotchart from Table2 to display hobby distribution.
I want a slicer that has all of the hobbies displayed and when I select any combination of hobbies, I want my hobby pivotchart to display information for just those hobbies AND my age pivotchart to display age distribution only for people that have that hobby.
How can I achieve this?
With my current setup the slicer affects hobby pivotchart, but the age distribution pivotchart remains the same. The slicer is connected to both pivottables.
It isn't working due to the direction of the relationship - ie Table 1 will slice Table 2 but not the other-way round.
You can get around this by creating a Measure for the value needed from Table 1 with a
CROSSFILTER(Table1[ID], Table2[ID], BOTH).