cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PBI_newuser
Post Prodigy
Post Prodigy

How to create a common slicer for 2 fact tables

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.

PBI_newuser_0-1611913512632.png

 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User III
Super User III

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
v-jingzhang
Community Support
Community Support

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.

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

parry2k
Super User III
Super User III

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Ashish_Mathur
Super User III
Super User III

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

lbendlin
Super User III
Super User III

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.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors