NGL...this is what I needed to see after hours of trying to figure it out with other videos. Only problem now is to figure out how to create new calculated measures!
Great video! Just one question. Can I change the order of the regions? I mean, it is possible to have europe at the top of the slicer, then America, etc? Thank you
Yes. Slicers will order items ascending, descending or from a custom list. Make a custom list and the slicer will order the items based on your custom list. My blog post shows you how. www.thesmallman.com/excel-custom-sort-with-vba
Is there any way to do this so a slicer filters 2 different tables of data - not pivot tables (they both have the same data in but I want to be able to filter different parts of my dashboard by different things)
You can clearly see I have 2 different tables of data and use the joining method to make the two tables talk to one another through a third table. You have to do as I do to get the same result as I have achieved. Hope that helps.
Marcus, I was trying to create a link between two slicers for a too long time, though it was urgent. I was thinking about creating a macro that read the option selected and applies it to the other slicer, but this gave me an apparently unsolvable -given my want of profound knowledge of VBA - infinite loop. I had tried a multitude of solutions, but everything failed. Really, really thank you. You deserve more views, likes, and subscriptions. At least one you got: mine.
I love this man! This worked like a dream! Thank you so much Marcus for showing me how to use Power Pivot to create the slicers that I can use to create data summaries to feed by charts! I’ve been trying to do this for so long!
So helpful 👍🏻 Is there any way to make the joining table/slicer dynamic so that if you added a new region to one of the data sources for example it would automatically be included in the slicer? I’m working on a dashboard and want a slicer for the months, which I’ll be adding to the source data as time goes on. Do I need to add the new month manually to the joining table each time?
Slicers by their nature are dynamic. When the new data is added to the dataset the slicer will update with the new fields on refresh. That is how it works.
@@thesmallman ok, but if you add data which includes an additional region in on of the main data sets, this wouldn’t automatically feed into the table that is driving the slicer would it? You’d need to add the additional regions into the unique value table you created to join the 2 sets of data?
@@chrislow123 yes this is correct, this would be able to be done with VBA automatically. You could have the unique items added to the list every time you clicked on the Region tab. As the main list grows so too does the support list. It is not too complex to create this procedure.
Hi Marcus, everything went fine until the last step. The thing is that only one pivot table(of the two that i had created) is being filtered at my end. I selected both the pivot tables in the report connection dialog box of the slicer but still only one pivot table is being filtered.
I was not able to see the pivot table from the other data source even after linking in the data model. Is there something I can do to refresh or make it appear?
Hi Marcus, how can you add another slicer for a field that's from the "many" table (ex: product, customer, quarter) and make it so it updates everywhere? For example, if you have a slicer for region, quarter, and customer, I want the region slicer to also control the quarter & customer slicer (i.e. gray out data if there's no sale from that customer)
You can't use slicers from the many table unless only data from the many table is in your pivot table. You have to use the 1 tables for all slicers if you want the slicer to work across multiple tables.
Thank you! The region is a common field in both of the tables. Right? In my case I have common values sort of but not exactly. For example, I have values like Sky (1) and Sky (2) in the other table and some values are like Sky (Blue), and in the other, table it will be 'Blue'. One word definitely matches but not all. How do I solve this? Thanks
Hi Peace - I think the problem with your logic is 'sort of'. That is not an option. Your like fields have to exactly match like a unique identifier. No exceptions!!!! Streamline your wording and it should work for you.
@@thesmallman Thank you! No, it did not work and it gives the message that values need to be unique and in my case, they are not because example Sky (Blue) and Blue. or Night and 'Day (Night)' Out of 60 values, it shows roughly 50 now once I delete duplicates when it should be around 25 unique values. Thanks
Hey there Marcus, Unfortunately, when trying to follow your method I get the ye olde "The relationship cannot be created because each column contains duplicate values. Select at least one column that contains only unique values." This is off because when I watch your video, you're connecting columns that have duplicate values with no issue. However, when I'm trying in mine I keep getting this error. It doesn't matter what columns I try to connect for the relationship, I still get this error.
Hi Christian - thanks for the question. Have a look at 3:43 and you will see that I do not connect two fields that have duplicates. Watch carefully. I connect EXPENSE (Region) to LOCATION (Region) - That is MANY to ONE. No duplicates in both tables and I do the same with Revenue REVENUE (Region) to LOCATION (Region). No duplicates in location table. You need to follow along exactly and you will eliminate the errors you are getting.
Hey man thanks! This is really helpful. Just one question - Is there a way to create the "Region" Table in PowerPivot itself? Reason I'm asking is because I might want to create multiple slicer, e.g. Month, Department, Region, Sector etc. It would be a pain to have to create individual table for each category, remove duplicates and link them to PowerPivot. Just wondering if there is a quick way to do so. Once again, thank you and really appreciate your work!
Hi Jack - thanks for your question. The quick way to do it is to create the table once, remove all the duplicates upload it to PP. It is the development at the start that saves you time in the future. You then just upload an amended table on the daily/weekly/monthly, whenever you report and that is it. You don't have to do it every single time you update the report but there needs to be some development time invested at the start. That is how I save time. Hope this helps. Marcus
@@bnunez44 You will notice whenever I want to report on something I have secondary lookup table that has no duplication. You set up tables with just unique weeks and a second table with unique years. You then connect these tables and the item you use to report your data is the newly created week and year tables. Hope that makes sense. It is really simple when you start doing it a lot. Keep practicing.
I need further help. I have created both relationships, my slicer has both report connections ticked and yet selecting an option on my slicer filters nothing.
Assuming you are using the same dataset, if you are getting a different result you need to look over the video again. If my end result and your end result are different there is a step you have missed.
Marcus, I am trying to create a relationship between my data sheets that are in my one file. I’m using a vendor ID to match up the data. Anyway, I’m getting an error “String review - relationship between vendor ID and vendor ID is a many to many relationship which is not currently supported. I took the vendor ids from the table that has the data I want and removed all duplicates and made a new table with just that data. I ensured both datasets are number format (although vendor ids are shown with 6 leading zeros 0000012345)
@@tammitaber5597 I was about to say send me the file. Feel free to email me if you have any questions as well, you have in the past and I will respond rather quick if you consider the time difference between the US and down under. : )
Well it has to be synced with at least one pivot table or you would not have a slicer at all. If you followed all the steps then it would have worked perfectly. Excel is consistent as it is a program, watch it again and follow every step. After you have a slicer make sure you choose REPORT CONNECTIONS and tick all your pivot tables.
NGL...this is what I needed to see after hours of trying to figure it out with other videos. Only problem now is to figure out how to create new calculated measures!
I'm glad it got you closer, thanks for your feedback.
Great video! Just one question. Can I change the order of the regions? I mean, it is possible to have europe at the top of the slicer, then America, etc? Thank you
Yes. Slicers will order items ascending, descending or from a custom list. Make a custom list and the slicer will order the items based on your custom list. My blog post shows you how. www.thesmallman.com/excel-custom-sort-with-vba
Is there any way to do this so a slicer filters 2 different tables of data - not pivot tables (they both have the same data in but I want to be able to filter different parts of my dashboard by different things)
You can clearly see I have 2 different tables of data and use the joining method to make the two tables talk to one another through a third table. You have to do as I do to get the same result as I have achieved. Hope that helps.
Thank you Marcus. What a simple, clear and apt explanation. Appreciate it.
You are most welcome Bhupinder.
Thanks a lot for this video. I was scratching my head a lot and got a solution by your video 👍👍
Great to hear! Appreciate the feedback.
Marcus, I was trying to create a link between two slicers for a too long time, though it was urgent. I was thinking about creating a macro that read the option selected and applies it to the other slicer, but this gave me an apparently unsolvable -given my want of profound knowledge of VBA - infinite loop. I had tried a multitude of solutions, but everything failed. Really, really thank you. You deserve more views, likes, and subscriptions. At least one you got: mine.
What a touching thing to say Bruno. Thank you ever so much.
Thank you very much! It leveled up my spreadsheet instantly!
Excellent! Glad it helped.
Thanks a lot! Best video about this tooic I ve seen so far!
You're very welcome!
I love this man! This worked like a dream! Thank you so much Marcus for showing me how to use Power Pivot to create the slicers that I can use to create data summaries to feed by charts! I’ve been trying to do this for so long!
Thanks Tammy - you are most welcome. I am glad to have helped you learn a little. All the very best Tammi.
So helpful 👍🏻
Is there any way to make the joining table/slicer dynamic so that if you added a new region to one of the data sources for example it would automatically be included in the slicer?
I’m working on a dashboard and want a slicer for the months, which I’ll be adding to the source data as time goes on. Do I need to add the new month manually to the joining table each time?
Slicers by their nature are dynamic. When the new data is added to the dataset the slicer will update with the new fields on refresh. That is how it works.
@@thesmallman ok, but if you add data which includes an additional region in on of the main data sets, this wouldn’t automatically feed into the table that is driving the slicer would it? You’d need to add the additional regions into the unique value table you created to join the 2 sets of data?
@@chrislow123 yes this is correct, this would be able to be done with VBA automatically. You could have the unique items added to the list every time you clicked on the Region tab. As the main list grows so too does the support list. It is not too complex to create this procedure.
Hi Marcus, everything went fine until the last step. The thing is that only one pivot table(of the two that i had created) is being filtered at my end. I selected both the pivot tables in the report connection dialog box of the slicer but still only one pivot table is being filtered.
I have this issue too.
I was not able to see the pivot table from the other data source even after linking in the data model. Is there something I can do to refresh or make it appear?
Thank You for the insight. Much appreciated
You are most welcome.
Hi Marcus, how can you add another slicer for a field that's from the "many" table (ex: product, customer, quarter) and make it so it updates everywhere? For example, if you have a slicer for region, quarter, and customer, I want the region slicer to also control the quarter & customer slicer (i.e. gray out data if there's no sale from that customer)
You can't use slicers from the many table unless only data from the many table is in your pivot table. You have to use the 1 tables for all slicers if you want the slicer to work across multiple tables.
Thanks a lot, quick to understand
No problem Arjun. Thanks for interacting with the page. :)
Thank you! The region is a common field in both of the tables. Right? In my case I have common values sort of but not exactly. For example, I have values like Sky (1) and Sky (2) in the other table and some values are like Sky (Blue), and in the other, table it will be 'Blue'. One word definitely matches but not all. How do I solve this? Thanks
Hi Peace - I think the problem with your logic is 'sort of'. That is not an option. Your like fields have to exactly match like a unique identifier. No exceptions!!!! Streamline your wording and it should work for you.
@@thesmallman Thank you! No, it did not work and it gives the message that values need to be unique and in my case, they are not because example Sky (Blue) and Blue. or Night and 'Day (Night)' Out of 60 values, it shows roughly 50 now once I delete duplicates when it should be around 25 unique values. Thanks
@@shoppersdream If you make the items unique it will work. The platform works on Unique Identifiers. Without them you will always fail. Best of luck.
Hey there Marcus,
Unfortunately, when trying to follow your method I get the ye olde "The relationship cannot be created because each column contains duplicate values. Select at least one column that contains only unique values."
This is off because when I watch your video, you're connecting columns that have duplicate values with no issue. However, when I'm trying in mine I keep getting this error. It doesn't matter what columns I try to connect for the relationship, I still get this error.
Hi Christian - thanks for the question. Have a look at 3:43 and you will see that I do not connect two fields that have duplicates. Watch carefully. I connect EXPENSE (Region) to LOCATION (Region) - That is MANY to ONE. No duplicates in both tables and I do the same with Revenue REVENUE (Region) to LOCATION (Region). No duplicates in location table. You need to follow along exactly and you will eliminate the errors you are getting.
Hey man thanks! This is really helpful.
Just one question - Is there a way to create the "Region" Table in PowerPivot itself? Reason I'm asking is because I might want to create multiple slicer, e.g. Month, Department, Region, Sector etc.
It would be a pain to have to create individual table for each category, remove duplicates and link them to PowerPivot. Just wondering if there is a quick way to do so.
Once again, thank you and really appreciate your work!
Hi Jack - thanks for your question. The quick way to do it is to create the table once, remove all the duplicates upload it to PP. It is the development at the start that saves you time in the future. You then just upload an amended table on the daily/weekly/monthly, whenever you report and that is it. You don't have to do it every single time you update the report but there needs to be some development time invested at the start. That is how I save time. Hope this helps. Marcus
@@thesmallman I'm trying to do the same but by week # and year. How would I go about getting rid of duplicates when thats what I am filtering by?
@@bnunez44 You will notice whenever I want to report on something I have secondary lookup table that has no duplication. You set up tables with just unique weeks and a second table with unique years. You then connect these tables and the item you use to report your data is the newly created week and year tables. Hope that makes sense. It is really simple when you start doing it a lot. Keep practicing.
Is it also possible to connect a regular table to a slicer?
Yes it is possible to connect a table to a slicer. Slicers provide the option to connect to a Pivot table or an Excel table.
The slicer can Use With Different dataset that more than 2 tables?
I have reports with over 40 tables.
@@thesmallman thank you
@@manatchaya1589 you are most welcome. :)
I need further help. I have created both relationships, my slicer has both report connections ticked and yet selecting an option on my slicer filters nothing.
Assuming you are using the same dataset, if you are getting a different result you need to look over the video again. If my end result and your end result are different there is a step you have missed.
THANK YOU
Marcus, I am trying to create a relationship between my data sheets that are in my one file. I’m using a vendor ID to match up the data. Anyway, I’m getting an error “String review - relationship between vendor ID and vendor ID is a many to many relationship which is not currently supported. I took the vendor ids from the table that has the data I want and removed all duplicates and made a new table with just that data. I ensured both datasets are number format (although vendor ids are shown with 6 leading zeros 0000012345)
I figured it out! Never mind. Thanks!
@@tammitaber5597 I was about to say send me the file. Feel free to email me if you have any questions as well, you have in the past and I will respond rather quick if you consider the time difference between the US and down under. : )
Marcus Small thanks so much! You truly are the best. I love that you share your knowledge so freely! It’s priceless!
Thank you...
You're welcome!
Hi,
I followed all the steps but my slicer is not synced with any of the tables
Well it has to be synced with at least one pivot table or you would not have a slicer at all. If you followed all the steps then it would have worked perfectly. Excel is consistent as it is a program, watch it again and follow every step. After you have a slicer make sure you choose REPORT CONNECTIONS and tick all your pivot tables.
👍