lookup with 2 search columns

[RMV]

Hi, I need to get a value from another table, with 2 search columns I tried to use this formula: LOOKUPVALUE = (Table2[ColumnToRetrieve], Table2[Entity], Table1[Entity], Table2[No.], Table1[No.]) for example: Table2 Entity No. ColumnToRetrieve A 1 2000 B 2 3000 What I need in Table1 Transaction Entity No. ColumnToRetrieve 123 A 1 2000 234 A 1 2000 345 B 2 3000 However, the formula I'm currently using returns blank value at Table1. Need advise what formula should I use. Thanks

[RMV]

---

@RMV wrote:

Hi, I need to get a value from another table, with 2 search columns I tried to use this formula: LOOKUPVALUE = (Table2[ColumnToRetrieve], Table2[Entity], Table1[Entity], Table2[No.], Table1[No.]) for example: Table2 Entity No. ColumnToRetrieve A 1 2000 B 2 3000 What I need in Table1 Transaction Entity No. ColumnToRetrieve 123 A 1 2000 234 A 1 2000 345 B 2 3000 However, the formula I'm currently using returns blank value at Table1. Need advise what formula should I use. Thanks

---

Hi,

I need to get a value from another table, with 2 search columns

I tried to use this formula:

LOOKUPVALUE = (Table2[ColumnToRetrieve], Table2[Entity], Table1[Entity], Table2[No.], Table1[No.])

for example:

Table2

Entity     No.      ColumnToRetrieve

A             1           2000

B             2           3000

What I need in Table1

Transaction    Entity    No.    ColumnToRetrieve

123                  A          1        2000

234                  A          1        2000

345                  B           2       3000

However, the formula I'm currently using returns blank value at Table1.

Need advise what formula should I use. Thanks

[Ashish_Mathur]

Hi @RMV,

This works fine for me

=LOOKUPVALUE(Table2[ColumnToRetrieve],Table2[Entity],[Entity],Table2[No.],[No.])

[Zubair_Muhammad]

Try This

=
CALCULATE (
    VALUES ( Table2[ColumnToRetrieve] ),
    FILTER ( Table2, Table2[Entity] = Table1[Entity] && Table2[No.] = Table1[No.] )
)

[RMV]

Hi @Zubair_Muhammad,

Tried your advise.

However, when I used the formula, the columns at Table1 is not recognized.

Need further advise. Thanks.

[Zubair_Muhammad]

Hi @RMV

Please type the formula and use Power BI's intelliesense to select Table1 Columns.

It could be that you copied and pasted the formula and there was a slight difference in column names.

This formula works fine with me with your data.

[RMV]

Hi @Zubair_Muhammad & @Ashish_Mathur,

Tried both of your formula. I typed it, and not just copy paste it.

But, I still can't manage it.

Do I have to create relationship first, or is there anything I need to look at?

[Zubair_Muhammad]

Hi @RMV

What error do you get this time?

Could you embed an image of Table2 in Power Pivot Data Model?