Connecting Slicer to different data sources

[sammilan]

Hi,

I need help with mapping single slicer for different data source.

I have 2 data sources that have repeating entries. I need to set up a slicer that filters between both the data source for which I have pivot tables in place.

To explain :
1. Data source 1 : This contains details of all the products with their categories.
i.e. products from category A, category B, category C till category R.
I have sales data for different products for last 3 months, aggregated at weekly level.
I have created pivots for revenue, sales unit, gap to plan and performance week over week.
I have set up slicer for category.
So when i click on the different categories, I get the performance elements for the specific category.

2. Data source 2 : This contains details of the same products but by region (North Zone, South Zone, East Zone and West Zone)
Again for this, I have multiple pivot tables for revenue, sales unit, gap to plan and performance week over week.
As of now, I have separate slicers to drill down the performance of the products by region.

I use a SQL query to pull the data from the warehouse and the data is stored on different tables in the data-warehouse and hence cannot club the 2 sources.
I tried using functions in SQL to club the 2 sources but that resulted in a massive sized file that crashed before it opened. (I have 18 categories x 5 zones x 3 months x 4 weeks)

Is there a method by which I can connect the slicer to both the data?

I tried using power pivot and building linked tables but that didn't work because I don't have a unique value in either table. (The products repeat every week and there isn't a unique identifier).

Any help here is much appreciated as it will save me considerable time in comparing the data.

 

[scottsen]

I am getting the feeling you are not using the data model / Power Pivot for this. Am I wrong?

 

[SpillerBD]

Intriguing....
I see the OP indicated

I tried using power pivot and building linked tables but that didn't work because I don't have a unique value in either table. (The products repeat every week and there isn't a unique identifier).

Scottsen, you know a procedure with PowerPivot to do this at some level?

 

[scottsen]

Ya, I missed that

I don't really see anything in the description that makes me think "oh, that isn't a good fit for power pivot". If required, you may have to build a separate lookup table for products (and maybe categories, dates, whatever) that have just unique values... so that relationships can be built, and slicers attached to those lookup tables to filter both of the fact tables (if indeed 2 tables even make sense)

 

[TimRodman]

Can you add a new table called "product" to your data model that has two columns (product and category) and one row for each product?

Then you could join your two existing data tables to the new "product" table and put the slicer on the category field in the "product" table.

 

[sammilan]

Hi Guys,

Thanks for the suggestions - I will try the create a new table to be able to link the slicers. Will feedback if that works