Forum Discussion
Slicer In Table A filters Table B based on multiple conditions
So i have 'Table A', which is a list of unique customers. Table B is the total inventory, and a different column for each customer. If a customers column has the value '1', that means that the customer is eligable to purchase the product.
I want to put Table A into a slicer, so that when a selection is made then Table B will filter for total inventory that those customers are eligable for. I must be able to select multiple customers in the slicer. I don't see a straight forward way to do this since a give row in Table B can be eligable for multiple customers. I also need to be able to filter for all customers so that it shows total inventory
I tried to accomplish this using a switch function, but i'm only able to select one customer at a time.
Table A
| my Slicer Values |
| Customer A |
| Customer B |
| Customer C |
| Customer D |
| Customer E |
Table B
| Product | Total_Inventory | customerA | customerB | customerC | customerD | customerE |
| Hamburger | 1 | 0 | 1 | 1 | 1 | 1 |
| Hot Dog | 1 | 1 | 0 | 1 | 1 | 1 |
| French Fries | 1 | 0 | 1 | 1 | 1 | 1 |
| Soda | 1 | 1 | 0 | 1 | 0 | 1 |
| Cheese Burger | 1 | 0 | 1 | 1 | 1 | 1 |
| Ice Cream | 1 | 1 | 0 | 0 | 1 | 1 |
| Cake | 1 | 0 | 1 | 0 | 1 | 1 |
| Candy Bar | 1 | 1 | 0 | 0 | 0 | 1 |
| Sausage | 1 | 0 | 1 | 0 | 1 | 1 |
10 Replies
- jdbuchanan71
Super User
Anonymous
You are going to want to unpivot table B so you have customers in a column. Then you can join your customer table into the inventory table using the customer ID.
Product Customer Inventory Hamburger A 0 Hot Dog A 1 French Fries A 0 Soda A 1 Cheese Burger A 0 Ice Cream A 1 Cake A 0 Candy Bar A 1 Sausage A 0 Hamburger B 1 Hot Dog B 0 French Fries B 1 Soda B 0 Cheese Burger B 1 Ice Cream B 0 Cake B 1 Candy Bar B 0 Sausage B 1 The data then wouldn't aggregate correctly though. It would show more inventory than is truly available if more than one customer is selected.
For example, a single hot dog in the warehouse can be available to multiple customers (although not all customers). if i unpivot the data the report will show that there is more hot dogs in our inventory than are truly available in the warehouse.
- jdbuchanan71
You are probably going to need something like this where the total inventory lives on the products table.
Then you can write an inventory amount measure to check if there is a single customer selected and if not sum off the total inventory rather than the customer inventory.
Inventory Amount = IF ( HASONEVALUE ( Customers[Customer] ), SUM ( 'Customer Inventory'[Inventory] ), SUM ( Products[Inventory] ) )
