Lookup value in Power BI - Blank

[Anonymous]

Dear all,

Could you please advise below issue?

I use Lookup value function in Power BI Desktop, but there is problem with Blank value in search result and Not found value in search result (as below picture).

If I want Blank value in search result display as "Blank" and Not found value in search result => "Not found".

How can I adjust the DAX?

Thanks for your support!

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try this:

Column =
VAR _value =
    IF (
        'Table2'[Product] IN VALUES ( Table1[Product] ),
        LOOKUPVALUE ( Table1[Sun-product], Table1[Product], [Product] ),
        "Not Found"
    )
RETURN
    IF ( _value = BLANK (), "Blank", _value )

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below.

Lookup Subproduct =
SWITCH (
TRUE (),
LOOKUPVALUE (
'lookup table'[Sub-product],
'lookup table'[Product], SELECTEDVALUE ( Products[Product] )
) == "", "Blank",
NOT ISBLANK (
LOOKUPVALUE (
'lookup table'[Sub-product],
'lookup table'[Product], SELECTEDVALUE ( Products[Product] )
)
),
LOOKUPVALUE (
'lookup table'[Sub-product],
'lookup table'[Product], SELECTEDVALUE ( Products[Product] )
),
"Not Found"
)

https://www.dropbox.com/s/hblghiqhn5qjnue/nguyen.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[amitchandak]

@Anonymous ,

assume you lookup column is A1 (of its calculation)

coalesce([A1], "Not found")

or if(isblank([A1]), "Not found" [A1])

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8