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
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!

View solution in original post

4 REPLIES 4
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!

View solution in original post

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.

Highlighted
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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 55 members 1,471 guests
Please welcome our newest community members: