Matrix Table Filter issue

[MP-iCONN]

My scenario is I have a matrix table that consists of a the following rows:

Work Order ID | Parent Item ID |  Component Item ID |  Required Date 

Then some other Values but the most import value is a Calculated Column called Available Comp which is how much of the Component Item ID we have in stock to put toward building the Parent Item ID.  What I need to do is if the Available Comp is a negative value then mark that Work Order ID as SHORT, but if we have enough of the Availabe Comp and it is positive then mark as OK.

The table data would look somthing like this.  Then the expected behavior would be a slicer to filter on SHORT or OK and from the table below WO12345 would be SHORT because it has one negative value and the other WO22345 would be OK.   Because it is a matrix table is why I am struggling to figure out how to make that custom column to mark the work order as SHORT or OK

Work Order ID	Parent Item ID	Component Item ID	Required Date	Available Comp
WO12345	P1212	C1212	12/31/2022	5
		C1213	12/31/2022	10
		C1214	12/31/2022	-5
		C1215	12/31/2022	6
		C1216	12/31/2022	1
WO22345	P2134	C2222	1/15/2023	100
		C2223	1/15/2023	50
		C2224	1/15/2023	11
		C2225	1/15/2023	10

Any advice would be greatly appreciated!

[DataInsights · Accepted solution]

@MP-iCONN,

Try this calculated column, which can be used as a matrix filter:

Work Order Status = 
VAR vWorkOrderID = Table1[Work Order ID]
VAR vNegativeRows =
    FILTER (
        Table1,
        Table1[Work Order ID] = vWorkOrderID
            && Table1[Available Comp] < 0
    )
VAR vResult =
    IF ( ISEMPTY ( vNegativeRows ), "OK", "SHORT" )
RETURN
    vResult

[v-xinruzhu-msft · Accepted solution]

Hi @MP-iCONN 

You can create a new column:

Column = VAR A=FILTER('Table','Table'[Work Order]=EARLIER('Table'[Work Order])&&'Table'[Available]<0)
RETURN IF(COUNTAX(A,[Work Order])>0,"Short","Ok")

Best Regards,

^{Yolo Zhu}

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

[MP-iCONN]

Actually I was able to do a merge queries in Power Query on both of those tables and once I made all the necessary columns I was then able to use this DAX that you gave and it worked out great.  Thank you very much!

[MP-iCONN]

These look like great solutions but I believe I should have mentioned the work order ID and Available Comp are in two different tables.  They are related tables but related by Many to many with Both direction by the Parent ID in both tables.  Getting the available comp in these DAX is proving difficult because you can't use RELATED() with the many to many relationship.

[Vilmar]

Veja se o anexo aqui ajuda.

Vou explicar:

• Esta é a receita para produzir o item P1212;

• O item P1212 tem pedido para dezembro (115 unidades) e janeiro (170 unidades);
• De todos seu componentes, só tem estoque para produzir em dezembro, pois o componente C1214 não vai ter saldo suficiente:
  • Estoque: 1300
  • Total dos pedidos: 10 x (115 + 170) = 2850

[DataInsights · Accepted solution]

@MP-iCONN,

Try this calculated column, which can be used as a matrix filter:

Work Order Status = 
VAR vWorkOrderID = Table1[Work Order ID]
VAR vNegativeRows =
    FILTER (
        Table1,
        Table1[Work Order ID] = vWorkOrderID
            && Table1[Available Comp] < 0
    )
VAR vResult =
    IF ( ISEMPTY ( vNegativeRows ), "OK", "SHORT" )
RETURN
    vResult

[MP-iCONN]

I tested with your DAX code as well @DataInsights and it also worked.  Thank you very much to both you and @v-xinruzhu-msft !