Forum Discussion
One slicer for two tables
Hello,
I have two tables in PBI; actual values and target values. The values should be shown in a bar chart. The relationship is based on date. I want to add a few slicers to that, they come from target values. The fields are the same in both tables. My problem is, the slicers dont affect the whole chart, only the target part. I tried to delete date relationship and establish relationships by fields i want to have in slicers. It didnt help. I tryied to make table with unique values, didnt work as well. Any ideas?
Double check that you are creating the many-to-one in the right direction. It sounds silly, but it is easy to choose one-to-many rather than many-to-one, or vice versa, as it depends on which order the tables are listed in the relationship window. I know I've made that mistake before.
10 Replies
johnt75Super User
Create a new table with the distinct values from both the targets and actuals,
Slicer Table = DISTINCT ( UNION ( DISTINCT ( 'Targets'[Column] ), DISTINCT ( 'Actuals'[Column] ) ) )Link this to both the targets and actuals in one-to-many relationships, and you can now use values from the new table in slicers and visuals.
It is not possible to make the relationship one to many. Error is saying, target table contains double values. The calculation for target values shows total sum, the same number for each year. No idea how to handle that.
- johnt75
can you share a PBIX? you can post a link to Google Drive, OneDrive etc.
vivek31Resolver II
HI Malina36 ,
1) you can create a dimension table
Dim_Slicer_Table = DISTINCT(UNION(VALUES(TargetValues[SlicerField]), VALUES(ActualValues[SlicerField])))
Replace SlicerField with the column name you want to filter by.
2) create relationships
connect Dim_Slicer_Table to actual values
connect Dim_Slicer_Table to actual values
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.Thank you
Thank you. It doesnt work as it should. Probably bc of relationships. I can only habe m:n relationship. One to many is not possible.
