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. 

 

Does anyone have an example of a similar query?

 

 

 

  • 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.

4 Replies

  • 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.

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.