Forum Discussion

MAruna's avatar
MAruna
Icon for Helper I rankHelper I
3 years ago
Solved

Lookup with logic on multiple rows

I Have two different tables   Table 1:- I'd            I'd Type          Region 123.          Legacy 300           EmpId 145            CanID 301            EmpId 350            EmpId 150   ...
  • tamerj1's avatar
    tamerj1
    3 years ago

    MAruna 
    Please try

    =
MAXX (
    FILTER (
        UNION (
            SELECTCOLUMNS ( Table2, "@ID", Table2[Empid], "@Region", Table2[Region] ),
            SELECTCOLUMNS ( Table2, "@ID", Table2[Canid], "@Region", Table2[Region] )
        ),
        [@ID] = Table1[id]
            && [@ID] <> BLANK ()
    ),
    [@Region]
)
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi MAruna 

assuming no relationship between the two tables 

=
MAXX (
FILTER (
UNION (
SELECTCOLUMNS ( Table2, "@ID", Table2[Empid], "@Region", Table2[Region] ),
SELECTCOLUMNS ( Table2, "@ID", Table2[Canid], "@Region", Table2[Region] )
),
[@ID] = Table1[id]
),
[@Region]
)

MAruna's avatar
MAruna
Icon for Helper I rankHelper I
3 years ago

Thank you @tamerj,

In ID's field some rows are null values,by using your dax formula im getting region for null aslo.

Could you please help me to get rid off from null values 

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    MAruna 
    Please try

    =
MAXX (
    FILTER (
        UNION (
            SELECTCOLUMNS ( Table2, "@ID", Table2[Empid], "@Region", Table2[Region] ),
            SELECTCOLUMNS ( Table2, "@ID", Table2[Canid], "@Region", Table2[Region] )
        ),
        [@ID] = Table1[id]
            && [@ID] <> BLANK ()
    ),
    [@Region]
)