Lookup with logic on multiple rows

[MAruna]

I Have two different tables

Table 1:-

I'd            I'd Type          Region

123.          Legacy
300           EmpId
145            CanID
301            EmpId
350            EmpId
150            CanId

Table 2:

EmpId          CanId             Region
300                123               EA
301                145               NORTH
350                150                EAST

Logic :-  >= 300 EmpId
                <300  CanId

Q)Need to get Region In table 1 based on table2
can any one tell me the Dax function to get this

Thankyou in advance.

[tamerj1]

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]

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 · Accepted solution]

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

Based on ID's I am taking

[FreemanZ]

hi @MAruna 

how are the two tables related?