Forum Discussion

MP-iCONN's avatar
MP-iCONN
Icon for Resolver I rankResolver I
3 years ago
Solved

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 va...
Need Help
  • DataInsights's avatar
    DataInsights
    3 years ago

    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

     

     

  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    MP-iCONN
    Icon for Resolver I rankResolver I
    3 years ago

    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.

  • MP-iCONN's avatar
    MP-iCONN
    Icon for Resolver I rankResolver I
    3 years ago

    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!