Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
OrionTL
New Member

LookUpValue from another table after filtering on the spot

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 
AAA123
BBB456
CCC789

 

Table BL

Sales Order Hold Description Hold Date Resolved 
789Bad Part1/1/2022Yes
456   
789Bad Paint1/2/2022Yes
123Bad Part1/25/2022Yes
123Bad 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
AAA123Has Issue
BBB456No Issue
CCC789No 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!!

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

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"
    )

vyangliumsft_0-1662947977753.png

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:

vyangliumsft_1-1662947977755.png

 

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

View solution in original post

2 REPLIES 2
tamerj1
Super User
Super User

Hi @OrionTL 
The following should work fine

Current Issue =
IF (
    "No" IN CALCULATETABLE ( VALUES ( 'Table B'[Resolved] ) ),
    "Has Issue",
    " No Issue"
)
v-yangliu-msft
Community Support
Community Support

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"
    )

vyangliumsft_0-1662947977753.png

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:

vyangliumsft_1-1662947977755.png

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors