LookUpValue from another table after filtering on the spot

[OrionTL]

Hi,

I have two tables, where table A holds the primary unique IDs of each sales order and table B holds the information of each sales order:

Table A

Part Number	Sales Order
AAA	123
BBB	456
CCC	789

Table BL

Sales Order	Hold Description	Hold Date	Resolved
789	Bad Part	1/1/2022	Yes
456
789	Bad Paint	1/2/2022	Yes
123	Bad Part	1/25/2022	Yes
123	Bad Paint		No

I'm trying to create a column on table A to show the issue of each sales order but as you see there's a one-to-many relationship and I'm trying to create a DAX column to show if the sales order is on hold or not based of on the criterias below:

1) If there's no "Hold Description" the returned results should be "No Issue"

2) If there's a "Hold Description" and no "Resolved" text, then the result should be "Has Issue"

so essentially the out put table A with added column would be:

Part Number	Sales Order	Current Issue
AAA	123	Has Issue
BBB	456	No Issue
CCC	789	No Issue

My current DAX formula is stating that Sales Order "456" as "Has Issue" because one of the looked at column criterias in the lookupvalue is true:

Current Issue Status = 
    IF(LOOKUPVALUE(TableB[Sales Order], TableB[Sales Order], TableA[Sales Order], TableB[Resolved], BLANK(), Blank()) = BLANK(), "No Issue", "Has Issue")

Is there a way to filter "Table B" first to get rid of all the rows that have no current "Hold Descriptions" and then peform a LookUpValue on that filtered table??

Thank you!!

[tamerj1]

Hi @OrionTL 
The following should work fine

Current Issue =
IF (
    "No" IN CALCULATETABLE ( VALUES ( 'Table B'[Resolved] ) ),
    "Has Issue",
    " No Issue"
)

[v-yangliu-msft · Accepted solution]

Hi  @OrionTL ,

Here are the steps you can follow：

1. Create calculated column.

Column =
VAR _selecthold =
    SELECTCOLUMNS (
        FILTER (
            ALL ( TableB ),
            'TableB'[Sales Order] = EARLIER ( 'TableB'[Sales Order] )
        ),
        "1", [Hold Description]
    )
VAR _selectresolve =
    SELECTCOLUMNS (
        FILTER (
            ALL ( TableB ),
            'TableB'[Sales Order] = EARLIER ( 'TableB'[Sales Order] )
        ),
        "1", [Resolved]
    )
RETURN
    IF (
        'TableB'[Hold Description]
            IN _selecthold
            && 'TableB'[Resolved]
            IN _selectresolve
            && 'TableB'[Resolved] = "No",
        "Has Issue",
        "No Issue"
    )

Current Issue =
VAR _count =
    COUNTX (
        FILTER ( ALL ( TableB ), 'TableB'[Sales Order] = 'TableA'[Sales Order] ),
        [Column]
    )
RETURN
    IF (
        'TableA'[Sales Order]
            IN SELECTCOLUMNS ( 'TableB', "1", 'TableB'[Sales Order] )
                && "No"
                    IN SELECTCOLUMNS (
                        FILTER ( ALL ( TableB ), 'TableB'[Sales Order] = 'TableA'[Sales Order] ),
                        "2", [Resolved]
                    ),
        MAXX (
            FILTER (
                ALL ( TableB ),
                'TableB'[Sales Order] = 'TableA'[Sales Order]
                    && 'TableB'[Resolved] = "No"
            ),
            [Column]
        ),
        MAXX (
            FILTER ( ALL ( TableB ), 'TableB'[Sales Order] = 'TableA'[Sales Order] ),
            [Column]
        )
    )

2. Result:

Best Regards,

Liu Yang

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