Use Slicers to find the min value amongst 3 options

[mschwartz]

I am using Power BI for the S&OP process in our company.  I have brought in the information on how much inventory we have on hand and expected to received.  The user can look into multiple different products to view this information. 

We tend to sell multiple products together.  I am trying create a measure based on slicer selection that will compare the amount of inventory for each product and find the min value.  Currently, when selecting multiply products the inventory increases.  It would be useful to determine which product has less inventory use this as a constraint.  I know this will mean a disconnected table and the swith() function.

  the above two products are sold together.  If both of these products are selected the graph should be constrained by the bottom graph.

Thank you for the help,

Michael

[mschwartz]

Simon_Hou-MSFT,

This worked out perfectly.

Thank you for the help

Michael

[mschwartz]

If you can help answer one more inquiry.  The Dax formula provided works great when selecting multiple options but if there are no products selected, the measure needs to clear/ reset to showing all inventory available.  I would try using an if statement but I don't think calculate can be used in an if statement.

Thank you for the help,

Michael

[v-sihou-msft]

@mschwartz

You can try formula below:

Measure = IF(HASONEVALUE(Inventory[Product]),CALCULATE(MIN(Inventory[Inventory]),ALLSELECTED(Inventory[Product])),CALCULATE(SUM(Inventory[Inventory])))

Regards,

[v-sihou-msft · Accepted solution]

@mschwartz

If you only want to get the min inventory selected products, you can use ALLSELECTED() in CALCULATE() function.

Min Inventory = CALCULATE(MIN(Inventory[Inventory]),ALLSELECTED(Inventory[Product]))

You can put above measure into your chart.

Regards,