Filter a table to get products with low inventory

[martipe1]

I have three tables:

1. Item Master. I have the item codes and the Safety Stock e.g.

Item	Safety Stock
A	100
B	200
C	125

2. Inventory at Warehouse Level

Warehouse	Product	Inventory
W1	A	75
W1	B	50
W1	C	100
W2	A	20
W2	B	100
W2	C	50
W3	A	10
W3	B	10

3. Last table that might override Safety Stock in the Item Master table.

Item	Warehouse	Safety Stock
A	W3	50
C	W3	300
B	W1	100

If I don't filter the warehouse the result would be 
A Product is good as Inventory is higher than 100 (105)
B Product is low because inventory is 150 compared to 200
C Product is good 150 vs 125

If I filter by W3
A is low because 10 < 50
B does not show because there is no safety stock for W3
C is low because there is 0 and we need 300

I hope all the data an explanation was good and you can help me out.

Thanks!!

[v-linyulu-msft]

Hi, @martipe1 

Thanks for the reply from @Wilson_ , please allow me to provide another insight:  

First of all, I have a couple of questions, I'm having a discrepancy in my understanding of these values below, please give me some advice on these values. (I am now basing this on the data you provided the first time).

First of all, in the first chart I understand that the values in group B are added together, so in that case it should have a value of 160, but what you have provided is 150.

The safe value for C in the second chart should be 300 to satisfy your first request, but you provided a value of 100:

May I ask if there is something wrong with my understanding? If so, I hope you can give me some advice.

Regarding the issue you raised, my solution is as follows:

1.Based on the table requirements you provided, I have created the following three tables, table name "Item Master" and table name "WAREHOUSE" as standard tables and table name "Iventory at Warehouse Level" for the actual values you provided, the table views are as follows:

2.Based on your request, I created four measures and my DAX function is as follows:

True1 = 
var _sum1=
SUMX(
    FILTER(ALL('Iventory at Warehouse Level'),
    'Iventory at Warehouse Level'[Product]=MAX('Iventory at Warehouse Level'[Product])),[Inventory])
var _sum2=
SUMX(
    FILTER(ALL('Item Master'),
    'Item Master'[items]=MAX('Iventory at Warehouse Level'[Product])),[safety stock])
var _select=SELECTEDVALUE('WAREHOUSE'[Warehouse])
var _column=SELECTCOLUMNS(FILTER(ALL('WAREHOUSE'),'WAREHOUSE'[Warehouse]=_select),"test",[items])
var _if=
IF(
    MAX('Iventory at Warehouse Level'[Product]) in _column,
    SUMX(
        FILTER(ALL('Iventory at Warehouse Level'),'Iventory at Warehouse Level'[Product]=MAX('Iventory at Warehouse Level'[Product])&&
        'Iventory at Warehouse Level'[Warehouse]=_select),[Inventory]),BLANK())
var _if2=
    IF(
        _if=BLANK()&&MAX('Iventory at Warehouse Level'[Product]) in _column,0,_if)
return
IF(
    NOT(ISFILTERED('WAREHOUSE'[Warehouse])),
    _sum1-_sum2,
_if2
    )

True2 = 
var _sum1=
SUMX(
    FILTER(ALL('Iventory at Warehouse Level'),
    'Iventory at Warehouse Level'[Product]=MAX('Iventory at Warehouse Level'[Product])),[Inventory])
var _sum2=
SUMX(
    FILTER(ALL('Item Master'),
    'Item Master'[items]=MAX('Iventory at Warehouse Level'[Product])),[safety stock])
var _select=SELECTEDVALUE('WAREHOUSE'[Warehouse])
var _column=SELECTCOLUMNS(FILTER(ALL('WAREHOUSE'),'WAREHOUSE'[Warehouse]=_select),"test",[items])
var _value=
SUMX(FILTER(ALL('WAREHOUSE'),
'WAREHOUSE'[items]=MAX('Iventory at Warehouse Level'[Product])&&'WAREHOUSE'[Warehouse]=_select),[Safety Stock])
return
IF(
    NOT(ISFILTERED('WAREHOUSE'[Warehouse])),
    _sum1-_sum2,
_value
    )

Missing Inventory123 = 
[True1]-[True2]

The main purpose of the measure flag is to display a negative difference even if there is no selection.

Flag = 
IF(
    NOT(ISFILTERED('WAREHOUSE'[Warehouse]))&&[True1]<0,1,
    IF(
        ISFILTERED('WAREHOUSE'[Warehouse])&&[Missing Inventory123]<0,1,0))

Here you can add your falg as a measure to the Filter option box.

3.Here's my final result, which I hope meets your requirements.

Best Regards,

Leroy Lu

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

[martipe1]

Thank you for your detailed answer, Leroy.

Probably I should have started with the table relationship and the logic for my filters.

T1 = Item master
T2 = Inventory at warehouse level
T3 = Exception by warehouse-product

T1-T2 Relationship is one to many by item code
T3-T2 Relationship is one to many by itemcode-warehouse 

My logic is first look if there is a restriction in T3 and use that value for safety stock, if there is no restriction (null value) use the safety stock from T1.

If my understanding is correct, when I use BOTH for cross filter direction is like having a table that combines the two of them, therefore if I get an item-warehouse key in T2, I should get kind of a null value for safety stock in T3 and  then I should look for the safety stock value in T1.

I hope my logic makes sense to you.

Now back to your questions.

You are right, my bad

"The safe value for C in the second chart should be 300 to satisfy your first request, but you provided a value of 100:"

There is an exception at warehouse level.

I have to use the exception for that item code-warehouse key, and you are right again, it should show 300 instead of 100.

Regarding your measures, for True2, I have trouble replacing Safety Stock ( Iassume it belongs to my T1) as I don't get my tables to choose the field

'Item Master'[items]=MAX('Iventory at Warehouse Level'[Product])),[safety stock])

Same for, I assume Safety Stock comes from my T3

'WAREHOUSE'[items]=MAX('Iventory at Warehouse Level'[Product])&&'WAREHOUSE'[Warehouse]=_select),[Safety Stock])

Thanks in advance for your answer

[martipe1]

The output

No filter

Item	Inventory	Safety Stock	Missing Inventory
B	150	200	-50

Filter by W3

Item	Warehouse	Inventory	Safety Stock	Missing Inventory
A	W3	10	50	-40
C	W3	0	100	-100

Thanks!!

[Wilson_]

Hi martipe1,

Thanks for providing the data in an easy to copy paste format and an explanation of your logic. However, what output are you actually expecting to see?