Forum Discussion
Matrix Table Filter issue
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!
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 vResultHi 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.
6 Replies
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 vResultI tested with your DAX code as well DataInsights and it also worked. Thank you very much to both you and Anonymous !
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
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.
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.
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!
