One slicer for two tables

[Malina36]

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?

[vivek31]

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

[Malina36]

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.

[johnt75]

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.

[Malina36]

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.

[Malina36]

Not really, its a lot of intern data. But i will try to answer every question you may have. Generlly it doesnt work. I have tried a lot of measures and i always get wrong numbers. Normally it should be easy, unique table for slicer and thats it. But nothing works 😭

[johnt75]

what sort of values are you trying to slice by? are they text values, whole numbers, decimals ?

[Malina36]

I want to slice by text, like country, company. For each I did separate unique table (also every thing in one table). Both doesnt work. On x achsis i have years. Thanks for your time 😊

[johnt75 · Accepted solution]

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.

[Malina36]

Thanks 🙂  i will look in to that.