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