Understanding an Expression (Lookupvalue function)

[Manas1415]

Hi Everyone,

I'm fairly new to Power BI and facing some troubles in understanding a particular expression which was already built by someone.

Any kind of help is much appreciated.

Table 1:

Column_1    Column_2    Column_3

A                

B

C

                    A

                    D

                    E

F                  G

G                 H

H

                    I

                    J

Table 2:

Field_1        Field_2

A                  M

B                   N

C                  O

D                  P

E                   Q

F                   R

G                  S

H                  T

I                   U

J                   V

So Column_3 is a calculated column with the following DAX Expression:

Column_3 = IF(
                ISBLANK(
                    LOOKUPVALUE('Table 2'[Field_2],'Table 2'[Field_1],'Table 1'[Column_1])
                ),
                    LOOKUPVALUE('Table 2'[Field_2],'Table 2'[Field_1],'Table 1'[Column_2]),
                    LOOKUPVALUE('Table 2'[Field_2],'Table 2'[Field_1],'Table 1'[Column_1])
           )

Can someone help me understand this formula?

Why couldn't we use the following expression? (I tried using the following formula but it doesn't pull the correct values)

Column_3 = IF(
                ISBLANK(
                    Table 1[Column_1]
                ),
                    LOOKUPVALUE('Table 2'[Field_2],'Table 2'[Field_1],'Table 1'[Column_2]),
                    LOOKUPVALUE('Table 2'[Field_2],'Table 2'[Field_1],'Table 1'[Column_1])
           )

Can someone explain me what exactly is happening here?

Thanks in advance.

[v-yulgu-msft · Accepted solution]

Hi @Manas1415,

Column_3 = IF(
                ISBLANK(
                    LOOKUPVALUE('Table 2'[Field_2],'Table 2'[Field_1],'Table 1'[Column_1])
                ),
                    LOOKUPVALUE('Table 2'[Field_2],'Table 2'[Field_1],'Table 1'[Column_2]),
                    LOOKUPVALUE('Table 2'[Field_2],'Table 2'[Field_1],'Table 1'[Column_1])
           )

If LOOKUPVALUE function returns blank value based on 'Table 1'[Column_1], then, it changes to lookup results based on 'Table 1'[Column_2].

 

Column_3 = IF(
                ISBLANK(
                    'Table 1'[Column_1]
                ),
                    LOOKUPVALUE('Table 2'[Field_2],'Table 2'[Field_1],'Table 1'[Column_2]),
                    LOOKUPVALUE('Table 2'[Field_2],'Table 2'[Field_1],'Table 1'[Column_1])
           )

This formula is also correct. But you need to add the single quote as highlighted in below image.

Best regards,

Yuliana Gu