How to get values of matrix where the values are negative

[Anonymous]

Product	 2015-5	2015-9	2015-25
Tomato	 7899	 254	 78
Orange	  43	 466	-365
Banana	 -90	 32	     527
Spinach	  89	-647	173

I have stock data that looks like above, the columns are date in Year-Week format, while the rows are products. I want to capture the values of the products where the stock level is negative regardless of the date. If it is negative at any point of time, I want to capture the product name. For instance, here in my sample data above, Orange, Banana and Spinach are the values I want to get. 

Here is my trial DAX code below:

Stock Required = sumx(Inventory,Inventory[Stock Available])

Short Components = IF([Stock Required]>=0,BLANK(),FIRSTNONBLANKVALUE(Inventory[Product],1))

But this is not considering the date I assume, because some products with negative stock level are not being captured. 

Any help on this would be great. How can I achieve this with DAX please?

[v-jingzhang · Accepted solution]

Hi @Anonymous 

Assume your data table is like below, you can add a Flag column to mark whether a stock is negative.

Flag = IF(Inventory[Stock]<0,1,0)

Then create a measure to sum the flag column, put it into the filter pane and set value is 1. Products with any negative stock will be shown in the matrix.

Measure = IF(SUM(Inventory[Flag])>0,1,0)

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[amitchandak]

@Anonymous , Try a measure like

Stock Required = sumx(filter(summarize(Inventory,Inventory[product], Inventory[Date],"Stock" ,sum(Inventory[Stock Available])), [Stock]<0),[Stock])