• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

link multiple pivot tables to a 1 slicer (file attached)

NiTz

New Member
Hi All,

I don't understand why the connections is not working.
I want to filter both pivot tables with 1 slicer.

could you please help me?

thanks in advance....
 

Attachments

  • Chandoo.xlsx
    941 KB · Views: 3
Because you have 2 fields for projects in each of your tables and both your pivots.
So your slicer only filters one of them.
To make it work, you need to work on your data model in order to have a unique table of projects (with attributes) connecting to two fact tables.
 
@GraH - Guido thank you for your answer. what do you mean "with attributes"? I created new unique table of projects (sheet 7 column M), and linked it to the tables. what am I doing wrong?
when I did the same in other workbook where the data was not formatted as a table but just "regular" data, it worked. when I created the relationships I saw the pivot tables there and linked it to the project list. Now when I formatted all as a tables, I see in the relationships only the tables and not the pivot tables.
 
Hi NiTz,

See attached, I've corrected where you were wrong. I was fooled first by your pivots as I overlooked the common "project" table.
So the slicer must be on that table, not on one of the fact tables (CATS, OMS).
Also there is no need to have that field in the filter section of your pivot. Though I've left it in.

With attributes I meant some other fields that are linked and unique to the project. Example could be project number in your data.
 

Attachments

  • Copy of Chandoo-1.xlsx
    938.5 KB · Views: 4
thank you! you are magician! :) but how did you fix it? when I trying to do it on my own, and when I am adding the slice to the project table, I don't even have the option to link it to the Pivots.
Is there another way to do it but the this?
75654
 
The slicer connection was not an issue, the field used in the slicer was. It needs to come from the project table, not from the CATS, OMS tables.
You took it from OMS I believe. Then since there is no link between OMS and CATS the slicer can't work on both.

As a side note: seeing this "mistake" makes me wonder if you understand the basics of data modelling and DAX enough. I advice to invest in some books or trainings if possible. Certainly if you need this skill at work. Will help you avoid frustration and more important avoiding wrong reporting.
I'm also on this learning path, and even with the back-up of the books I've read, the video tutorials I followed, there are patterns that just overwhelm me and make feel stupid. It's on those moments you need to fall to the basic concepts and think (hard) to the point you suddenly get it and move on or should I say up.
 
Hi,
you wrote "It needs to come from the project table, not from the CATS, OMS tables" - but when I create it from the project table, as you can see in the print screen above, I can't connect it to the pivots, and it sort only the project table when I use it :(
Sure, I am definitely accept your advise , I do need to learn a lot more... but here specific, I did it few times without any problems when using ranges and not tables, and it was always worked. only know when I am using tables instead of ranges I am running into some issues.
thanks again for you time and support, I am really appreciate it!
 
got you know!
thank you very much.
I am going to look for more video about DAX and data modeling :)
 
Back
Top