link multiple pivot tables to a 1 slicer (file attached)
- Thread starter NiTz
- Start date
GraH - Guido
Well-Known Member
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.
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.
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.
GraH - Guido
Well-Known Member
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.
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.
GraH - Guido
Well-Known Member
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.
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!
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!
GraH - Guido
Well-Known Member
Might you have picked it from the project table on the sheet, not from the data model?
GraH - Guido
Well-Known Member
Good luck and enjoy the learning path.