cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rumittal Regular Visitor
Regular Visitor

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

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:

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
richbenmintz
Advisor

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

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!

4 REPLIES 4
Highlighted
richbenmintz
Advisor

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

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!

TCarrasquillo Regular Visitor
Regular Visitor

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

 

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 Regular Visitor
Regular Visitor

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

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.

rumittal Regular Visitor
Regular Visitor

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

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.

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 237 members 2,641 guests
Please welcome our newest community members: