Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rumittal
Microsoft Employee
Microsoft Employee

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
richbenmintz
Solution Sage
Solution Sage

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!



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


View solution in original post

4 REPLIES 4
TCarrasquillo
Helper II
Helper II

 

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.

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
Solution Sage
Solution Sage

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!



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors