Slicer From One Table Not Working on Related Table

[MD610]

This one has got me stumped because I have done the exact same thing with other sets of data.

I have 2 tables in Powerpivot:

1. Fact Table
2. Filter Table

There are 2 fields in each table that must be concatenated to create a join key. After creating the concatenated field in each table, the filter table is the table with unique values in the join key.

I then created a relationship between the 2 concatenated fields from each table.

I create a pivot using fields exclusively from the fact table and create slicers using fields exclusively from the filter table.

Nothing happens when I push a slicer. I still see the full set of data from the facts table in my pivot.

I can't figure out what I am doing wrong or missing. I have done this exact sort of thing with other sets of data and never had an issue.

I've tried redoing this scenerio from scratch several times with the same outcome each time. I'm stumped.

My ultimate goal is to relate the filter table to a second fact table so that one set of slicers can filter pivots from each table. Otherwise, if I was only using data from a single table, I would just use the RELATED() function to pull my filter fields into the fact table.

If anyone has any ideas or suggestions, I would really appreciate it. I'm hoping that I have just been staring at the computer screen too long and am just overlooking something simple.

 

[masplin]

first thing to check is in the relationship maanger does your relationship say "Yes". I've had this before when I think my relationship has bene established, but then looked here and it hasn't for some reason. Seems unlikely but first step. Have you tried created a RELATED column in your fact table just to prove the relationship is working?

Mike

 

[MD610]

Thanks for your response. Yes I have verified both of the things you suggest. The relationship is Active and I can pull data from the filter table into the fact table using RELATED just fine.

 

[masplin]

Just one more check which I cant imagine isnt ok is the slicer connections on the pivot table menu. Options/insert slicer/slicer connections. Just check the ones you expect are ticked.

 

[MD610]

Yes. All that was checked and confirmed. I finally gave up trying to figure this out strictly with PowerPivot. I did some preprocessing of my tables in SQL server to reformat everything I needed into a single table. Not a solution for everyone and it probably wouldn't work quite as well in every situaiton but for my needs it worked out. If you know a little SQL (or even just have some solid Access skills) sometimes it just makes life easier to address these issues in the source data.