How to create a common slicer for 2 fact tables

[PBI_newuser]

Hi,

I have 2 fact tables "Resource Availability" and "Work Order".

I would like to have common slicer for "Region" and "Product Line".

But, after I created the relationship for "Product Line" with the dimension table "Dim_ProductMapping", I can't create the relationship for "Region" with the dimension table "Dim_Region".

Please help.

[v-jingzhang]

Hi @PBI_newuser , have you tried the suggestions in other replies? It should work and then you can use the column in Dim table for your slicer.

[PBI_newuser]

Hi @v-jingzhang , it works. I have accepted one of them as solution. Thanks everyone!

[parry2k]

@PBI_newuser @lbendlin is right on track, make sure product line is a unique column, currently looks like it is not because it has many to many relationships.

What @Ashish_Mathur explained is good but not to confuse you already have one to many relationships with the dim_region table and no change required there.

In nutshell, just focus on the product mapping table and ask yourself if the product line is the right column to have a relationship. Logically it should be on the product.

Good luck! You are in safe hands with @lbendlin 

Cheers!

[Ashish_Mathur · Accepted solution]

Hi,

Follow these guidelines:

1. Ensure you have only unique entries (no blanks at all) in the Region column of the Dim_region table
2. Ensure you have only unique entries (no blanks at all) in the Product Line column of the Dim_ProductMapping table
3. Build a Many to One relationship (With Single cardinality) from the Region column of the Resource Availability table to the Region column of the Dim_Region table
4. Build a Many to One relationship (With Single cardinality) from the Region column of the Work Order table to the Region column of the Dim_Region table
5. Build a Many to One relationship (With Single cardinality) from the Product Line column of the Resource Availability table to the Product Line column of the Dim_ProductMapping table
6. Build a Many to One relationship (With Single cardinality) from the Product Line column of the Work Order table to the Product Line column of the Dim_ProductMapping table

Hope this helps.

[lbendlin]

Your DimProductMapping dimension table is incorrect.  Dimension tables need to have a unique key (in your case Product Line).  Correct that issue (clean up the duplicates) and then correct the search filter to be unidirectional from dimension to fact. After that everything else will work ok.