Multiple Slicers and Multiple Tables

[kpeebles]

Hello PBI Community, 

I am trying to get two slicers (really three if you include the date filter) to filter data on a graph however it has been a little difficult and my thought is it has to do with the relationships and the fact that the data is on different tables. Let me give you some background. 

This is what I have:

The bulk of my data comes from 6 different excel files. They are all identical in terms of the column titles (the data, of course is different) and the goal is to graphically represent columns from the 6 files on one single graph and be able to filter the results a couple different ways. I will call the six sets of data "A-F". Think of these as 6 different “regions” where sales take place and the regions are again, called A-F. Here they are loaded into PBI. 

I created a common Calendar and connected all the regions ("letters") to the calendar based on the date column. I then created a filter table with letters A-F which populates the slicer. I connected the filter with the data set and connected the data to the calendar. (*I only showed the connection with region A below, but all 6 are connected).

I set up the graph with the sales data and the filter as you can see below, and this is the result. 

 

As seen above, the first slicer works perfectly. I selected B, C and D and the data shown above are the sales for those regions.

The Problem:

The problem arises when I add the second slicer. The real problem is, I want the data above (in region A-F to be filtered by a separate set of data.) The second set of data comes from a 7th excel file. On this excel file, there are 11 columns that represent the 11 warehouses that the product comes from. Sales in the regions are directly tied to the production of the warehouses.

The data on the warehouse file is basically just a bunch of numbers that represent units that are being supplied to the regions. Any number of warehouses can be providing products to any of the regions at any given time.

So here is what I am trying to accomplish:

I want to be able to select Region A and Warehouse 1 and see the sales in Region A when Warehouse 1 was supplying product. I don’t care really about the number of units Warehouse 1 was supplying (because they are evenly distributed across all regions), I really just care whether Warehouse 1 was providing any products or not. Simply put, when Warehouse 1 did not send us anything, I want the graph to show no data, and conversely, when Warehouse 1 was supplying product, I want the graph to show the sales in the regions.

Here is what I have tried:

Solution 1:

I first tried to merge the warehouse data onto all the region files. Basically, I put all the warehouse data onto models A-F (in PBI). Once it was there, I wrote an equation that looked at each of the columns of data (1-11) and if there was a value in that column it replaced that value with “Warehouse 1”, “Warehouse 2”, “Warehouse 3” etc. for each respective warehouse (there was a separate column for each warehouse). If there was no value in the column the equation left the cell blank. The hopes would be that when I added the filter, it would graph the data when there was a value (Warehouse 1….) and not graph the data when the row was blank. I then created a Warehouse filter table similar to the sales filter that I showed above. The problem with this approach came when I had to make the relationship. The relationship between the warehouse filter had to apply to all 11 of the new columns in the sales table. I could not make a relationship with all 11 on each of the files A-F.

Solution 2:

I then wrote a measure that separated all 11 of the warehouses. I included the date and wrote an equation to create a filter column as shown below. I am not sure why this one did not work. 

These are the two main solutions I tried. I also tried combining the warehouse columns so that I have a single column that lists Warehouse 1, Warehouse 2, Warehouse 4, Warehouse 4,… and I tried having the filter look at the combined column but this did not work. Any help would be greatly appreciated.

Thanks!

-Kyle

[Sergii24]

Hi @kpeebles, so to summarize you want to filter data by the warehouse and then, if the warehouse supplied any qty to selected region in a given period of time, then show data on the graph, otherwise show nothing. Is it correct?

Few more questions:

• is there any specific reason why you don't group data from different regions (I suppose your initial 6 excel files) within the same table using "folder" action in PowerQuery?
• are you going to use filters with single selection (i.e. user can select only 1 region and 1 warehouse at a time)?
  • if not, how the graph should behave? Imagine I selected 3 regions (A, B, C) and 2 warehouses (1 and 2), warehouse 1 supplied goods to region A, while warehouse 2 to the region A and B. What result do you expect?

[kpeebles]

Hello @Sergii24. Thank you for your reply. Yes, I believe you summarized it correctly. Just to confirm though, I want to "filter data by the warehouse and then, if the warehouse supplied any qty to selected region in a given period of time, then show [sales] data on the graph, otherwise show nothing."

And as for your other questions, there is no particular reason I did not group the data. I am relatively new to PBI so this is the first time I am hearing about this option! I will do some additional research on it and see how I can implement the grouping option. 

And secondly, I will set up the filter so that you can only select one warehouse at a time but I would still like to be able to select multiple regions. 

Hope that helps clear up the confusion. 

Thanks, 

Kyle