topic Re: Lookup with logic on multiple rows in DAX Commands and Tips

Lookup with logic on multiple rows

MAruna — Sat, 08 Apr 2023 05:36:16 GMT

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.

Re: Lookup with logic on multiple rows

FreemanZ — Sat, 08 Apr 2023 06:35:01 GMT

hi MAruna

how are the two tables related?

Re: Lookup with logic on multiple rows

MAruna — Sat, 08 Apr 2023 06:58:01 GMT

Based on ID's I am taking

Re: Lookup with logic on multiple rows

tamerj1 — Sat, 08 Apr 2023 07:37:11 GMT

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

Re: Lookup with logic on multiple rows

MAruna — Sat, 08 Apr 2023 08:56:36 GMT

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

Re: Lookup with logic on multiple rows

tamerj1 — Sat, 08 Apr 2023 09:09:10 GMT

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