How to slice data in different charts from different tables based on same slicer ?

[rumittal]

Hi,

I have two tables each of which have three identical columns - Team, TestSelectionBucket, ScenarioOwnerTeam.

I am creating two charts based on each of them, and want to add three slicers at the page level - such that when the values are selected in these slicers for Team, TestSelectionBucket, ScenarioOwnerTeam, corresponding data reflects in both the charts.

As of now, slicer is able to slice data only for the chart , from whose table we are deciding the Field valur for it.

Is there a way to slice all the charts in the page in this context ? 

Or have some page level filter that can work across both charts having same column name ?

Please refer below image for complete understanding:

[TCarrasquillo]

Hi,

I  think all you need to do is edit the interactions for your two charts. Select a slicer, go to format and click edit interactions. When you hover over your charts you should see a few icons on the top left corner. Click on the funnel/filter icon for both charts.

[rumittal]

Hi,

I guess below solution works only when both charts that you are trying to slice with the help of slicer have some relationship defined to it in the data model. Else just two individual charts with individual table sharing a common column , is not being able to sliced by the slicer created on values of the common  column.

[richbenmintz · Accepted solution]

Hi @rumittal,

you require a set of dimensions and a star schema. as an example you can create a 'scenario owner' dimension either at the data source with a view that gets the distinct values from both of your fact tables, with a dax calculated table

Scenario Owner = DISTINCT(UNION(VALUES(consistency_across[scenarioowner]), VALUES('query1'[scenarioowner])))

or within power query. Once you have the dimension create a 1->* relationship from the dimension to both fact tables, then add the dimension attribute as your slicer or filter element. 

Voila a single filter to control them all!

[rumittal]

Thanks for the help, it worked ! 

In case someone doesnt know Power Query M language, they can do below to create a dimension table (got it from powerbi learning PDF)

To create a relationship in this case, we need to create a logical dataset of all the CustomerNames across the two datasets. In the Query tab, you can use the following sequence to create the logical dataset:

1. Duplicate both queries, naming the first Temp and the second CustomerNames.
2. In each query, remove all columns except the CustomerName column
3. In each query, use Remove Duplicate.
4. In the CustomerNames query, select the Append option in the ribbon, select the query Temp. 5. In the CustomerNames query, select Remove Duplicates. Now you have a dimension table that you can use to relate to CustomerIncidents and WorkItems that contains all the values of each.

Now use Dimension Table : CustomerNames, to create slicer . If power BI doesn’t create relationship by itself, go to Manage Relationship>Edit >create 1:* relationship from dimension to fact tables.