Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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

ProductTotal_Inventory  customerA  customerB  customerC  customerD  customerE  
Hamburger101111
Hot Dog110111
French Fries101111
Soda110101
Cheese Burger101111
Ice Cream110011
Cake101011
Candy Bar110001
Sausage101011
10 REPLIES 10
jdbuchanan71
Super User
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
Anonymous
Not applicable

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.

dax
Community Support
Community Support

Hi @Anonymous , 

I am not sure your requirement, you could referto my sample for details. If this is not waht you want, please correct me and inform me your expected output.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Anonymous
Not applicable

I need a single column in the matrix that displays counts available inventory. This column must filter for the selected customers (whether it be a single, multiple, or all customers).

 

 

You are probably going to need something like this where the total inventory lives on the products table.

jdbuchanan71_0-1595349559630.png

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] ) )

jdbuchanan71_1-1595349710188.pngjdbuchanan71_2-1595349733691.png

 

Anonymous
Not applicable

I've already done something like this. I am able to have either one or all customers selected. However this won't work if multiple (but not all) customers are selected from what i can tell. 

dax
Community Support
Community Support

Hi @Anonymous , 

Did you refer to my sample? it could work when you select multiple customers, and the Total column will show the status of storage. If this is not what you want, please correct me and inform me more detailed infromation(your expected output).

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Your sample was close to what i needed but the grand total column isn't showing accurate aggregations. I only need the grand total column to work. (not the grand total of rows)

dax
Community Support
Community Support

Hi @Anonymous ,

If possible, could you please inform me your expected output(grand total )? In  my logic, when grand total =1, it represents that it have storage.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

My data is structured differently than the example you attached. The formula doen't work. In my case i cannot restructure the data

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.