Forum Discussion

GMCMORROW's avatar
GMCMORROW
New Member
4 years ago
Solved

Take value from another column if blank

I have 2 SQL tables, customer and address table, that i need to use logic on.    I want to take a value from the address table but if its blank i need to take a value from the Customer table.    ...
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi GMCMORROW ,

     

    I have built a data sample,

     

     

    You could simply use LOOKUPVALUE():

     

    Column = LOOKUPVALUE(BPADDRESS[EXTNUM_0],'BPADDRESS'[BPCNUM_0],[BPCNUM_0],[BPCNUM_0])

     

     

     

     

     

    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.

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
4 years ago

Hi GMCMORROW 

Can you show a sample of the tables? Are they linked by a relationship? How?

How are the values in both tables linked?


 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

GMCMORROW's avatar
GMCMORROW
New Member
4 years ago

Hi AlB

 

Thanks for responding.

 

I have two tables (BPCUSTOMER & BPADDRESS) that are linked by a customer number BPCNUM_0 a unique value on both tables. 

 

I want to take the field EXTNUM_0 from the BPADDRESS table but if its blank I want to take the field BPCNAM_0 from the BPCUSTOMER table. 

 

I hope thats enough data to help!

 

 

 

 

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    4 years ago

     

    GMCMORROW 

    Always show a sample of the tables; it makes everything easier. This would be best done in power query but if you want it in DAX create a calculated column in BPCUSTOMER:

    NewCol =
VAR aux_ =
    LOOKUPVALUE (
        BPADDRESS[EXTNUM_0],
        BPADDRESS[BPCUSTOMER], BPCUSTOMER[BPCUSTOMER]
    )
RETURN
    IF ( NOT ISBLANK ( aux_ ), aux_, BPCUSTOMER[BPCNAM_0] )

     If this doe not work i need a smaple of the tables with an example and the expected result, plus the specification of the relationship (one to many and who filters who?)

     

    		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.