cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

One slicer for two tables-relations

I have two tables and one slicer. The slicer controls the table Query1 which contains sales data, but I am also pulling in some CRM data from another database. As I change filter settings in the slicer, the sales data change, just as expected, but I would also like the 'Visits' table to change. So basically, when I change the slicer by clicking on a sales person, the graph will change from showing a total, to show the sales figures for the sales person. 

 

In the same way, I would like the visits to change from a total to the number of visits for the sales person. Since both tables contain multiple transactions, I have created a relationship through a third table, the Customer table. The relational field is the customer number.

relationship.PNG

 

Here my knowledge ends, and I am not sure how to progress, or even if I am thinking correctly. Anyone who can tell me how to go about this?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver III
Resolver III

Re: One slicer for two tables-relations

Ok - so a slicer that uses a field in the Customer table will filter data in both of the other tables. Try adding Sales Area to a slicer and you should see what I mean. 

 

So, if you want a slicer with Sales Rep name to filter both tables you will need to somehow get Sales Rep name into the Customer table (or create a new 'lookup' table with Sales Rep and unique Customer Numbers).

 

One way to do this is to follow these steps:

 

1. Click Edit Queries

2. Right click on Query1 and select Reference

3. In the new query that is created, remove all columns except Sales Rep and Customer Number

4. Right click on the Customer Number column and select Remove Duplicates

 

Then you can create a relationship between customer number in this new table and customer number in the Query1 and Visits tables. 

 

Finally, use Sales Rep name from this new table in your slicer

 

(Note, though, that any customers in the Visits table that aren't in the Query1 table will not be linked to a Sales Rep name. It would be better if you had a table with all customers and their Sales Reps...but that may not be readily available)

View solution in original post

4 REPLIES 4
Highlighted
Resolver III
Resolver III

Re: One slicer for two tables-relations

Do you have the Sales Rep's name in the customer table? If so, that's the field you should use in your slicer. 

Highlighted
Helper II
Helper II

Re: One slicer for two tables-relations

Not the name, but the Sales area. But the Sales area is not unique in any of the tables, since more than one customer can have the same Sales person.

Highlighted
Resolver III
Resolver III

Re: One slicer for two tables-relations

Ok - so a slicer that uses a field in the Customer table will filter data in both of the other tables. Try adding Sales Area to a slicer and you should see what I mean. 

 

So, if you want a slicer with Sales Rep name to filter both tables you will need to somehow get Sales Rep name into the Customer table (or create a new 'lookup' table with Sales Rep and unique Customer Numbers).

 

One way to do this is to follow these steps:

 

1. Click Edit Queries

2. Right click on Query1 and select Reference

3. In the new query that is created, remove all columns except Sales Rep and Customer Number

4. Right click on the Customer Number column and select Remove Duplicates

 

Then you can create a relationship between customer number in this new table and customer number in the Query1 and Visits tables. 

 

Finally, use Sales Rep name from this new table in your slicer

 

(Note, though, that any customers in the Visits table that aren't in the Query1 table will not be linked to a Sales Rep name. It would be better if you had a table with all customers and their Sales Reps...but that may not be readily available)

View solution in original post

Highlighted
Helper II
Helper II

Re: One slicer for two tables-relations

Sweet! Took me a while to understand, but now it works just as I wanted it to.

Thanks!

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors