One table puts multiple filters on another table

Question

I have two tables that are in their imported form are the same exact sales table. The first table I left it exactly as it came in with the columns called: salesman, manager, sale date, product category, product #, and sales dollars. Let's just call this the "Detail Table". The second table was imported exactly as the first one was, but I grouped each salesman by sales date and product category. Let's just call this the "GroupBy Table".There is a many to many relationship between the two tables. I created table visuals in report view. Both of them, in report view, are exactly how I described them. I set the Edit Interactions so that the GroupBy Table filters the Detail Table. However, it only filters the other table for that salesman (that is the column I used for the many to many relationship between the two tables). What I would prefer it to do is to filter out for the entire row of the Group By row that I click on. I don't know if this is possible yet. Here is an example of what I mean.&nbsp;&nbsp;Group TableSalesman&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Sales Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Product CategoryBob Smith&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1/1/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;PiesBob Smith&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/1/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CookiesJane Doe&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1/1/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; PiesJane Doe&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1/2/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Pies&nbsp;What I want is that if I clicked on the top row of the group table above, it would filter my detail table to show all of Bob Smith's sales records on 1/1/2018 in the Pies product category. Is this possible? If so how do I do this? Thanks!

ragnarock1982 · Answer

Hi,&nbsp;Not sure if I've followed you correctly, but are you wanting to 'drill down' on the second chart, based on what you click on the first?&nbsp;If so, I don't think this is possible at the moment, unfortunately. I was looking for this exact thing. However, another way round this is the 'Drillthrough' function. Create another page in the report with detailed information etc. filtered by 'Salesperson'. Then when you right click the sales person on the first page, you can 'Drillthrough' to the second page and it will be filtered/drilled by just that one sales person.&nbsp;Hope this helps? If I'm off track, please do let me know. :smileyhappy:

anonymous · Answer

Ragnarock1982&nbsp;Thanks for the response! However, I do think you misunderstood me and rereading my response I understand why. I don't have a chart at all. I'm using two table visuals. When clicking on one of the rows in the GroupBy table that I explained above it filters the Details table for all of the records for which that specific salesman had sales. Here is another, hopefully more clear, example:GroupBy TableSalesman&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Product CategoryBob Smith&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/1/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;PieBob Smith&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1/1/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CookiesJane Doe&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/1/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; PieJane Doe&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/1/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CookiesBob Smith&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/2/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; PieBob Smith&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1/2/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CookiesJane Doe&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1/2/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Pie&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;***And so on I think this gets the point across***My Details Table looks like this&nbsp;Salesman&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Product Category&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Sales Amount&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CustomerBob Smith&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Cookies&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/1/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Cookie MonsterJane Doe&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Pie&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1/1/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Joe JacksonJane Doe&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Cookies&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/1/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $7&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;John ThompsonBob Smith&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Pie&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/1/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $13&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Jose GutierrezJane Doe&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Pie&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/2/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Brad Washington'Bob Smith&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Cookies&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/2/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Sarah Smith&nbsp;So when I click in my GroupBy Table right now it only filters my detail table by Salesman. So if I clicked the first line (in my groupby table) it would give me all of the Bob Smith rows in my Details Table (the 1st,4th, and 6th lines in my example). However, what I want my GroupBy Table to filter for in my details table is when I click the first line (of GroupBy) to filter for all of the lines where Bob Smith made sells on 1/1/2018 in the "Pie" product category. So if I clicked the first line of my Group By table it would filter the Details table to where I would only see line 4 in my detail table (since this is the only line in my detail table where Bob Smith on the date 1/1/2018 sold something in the "Pie" product category). Let me know if I can elaborate more on this, and again thanks for the reply.

ragnarock1982 · Answer

Hi Anonymous&nbsp;, Gotcha!&nbsp;Basically, you would need to apply 3 filters to the second table based on the click on the first table. (3 filters as there are 3 columns in the first table)&nbsp;I may not have explained that right, but I do know exactly what you mean.&nbsp;However, rather than clicking on a 'Line', when you are clicking on 'Bob Smith', you are filtering the page by 'Bob Smith', not the line. 'Bob Smith' is a value within your table.&nbsp;Unfortunately though, I'm not aware of a way to do what you are looking for. I have a similar matrixes that I'm working with and I had to use the Drillthrough function to get this to work. Which works better than I had imaged by the way :smileyvery-happy:&nbsp;If anyone does give you a better idea though, please do let me know. .&nbsp; :smileyhappy:&nbsp;&nbsp;

anonymous · Answer

Ragnarock1982,&nbsp;See here's the odd part about it. I have a date slicer on right now, but it won't filter the date for both of the tables. If I have the date field from one table in it, it will only work for that table. If I use the date field from the other table it only works for the other table. Like I said in the first post, these were originally the same exact table when they were brought in. I just used the group by function to modify one of the tables. It has to be an issue with the relationships, but I can't figure it out. A many to many relationship is exactly what kind of relationship there is. I have cross-filter direction set to both, and I don't see why that would affect anything anyways.&nbsp; Right now, I have the two tables linked by Salesman. Interestingly, if I use Salesman as my slicer, it works for both of the tables. I don't understand why the same doesn't work for date or product category.&nbsp; Any tips on where to go from here would be greatly appreciated. I haven't used PowerBI in about 2 and a half years up until the beginning of this week.

anonymous · Answer

Bump

Forum Discussion

One table puts multiple filters on another table

6 Replies

Related content

Filtering a table with multiple conditions from multiple switches pulled from another table

Multiple Years Filter

Filter depending on another filter

Slicer Filtering Another slicer

DAX FILTER with multiple criteria