Forum Discussion

djerryanderson's avatar
djerryanderson
Regular Visitor
2 years ago
Solved

IF THEN to select particular columns

New to DAX - just started yesterday!!!

 

Im trying to build a table that looks at contact info and shows whether or not we will send them a christmas gift. The address, however, should be their home address unless that is NULL...then we pick the business address. To do the NULL check I just look at the home address city and if there is no value there use the business addres. If anyone can help with the correct syntax I would appreciate it.

 

EVALUATE
    SELECTCOLUMNS(
        Contact,
        Contact[FirstName],
        Contact[LastName],
        Contact[Christmas_Gift__c],
        IF(
            Contact,
            [Home_Address__City__s] = BLANK,
            SELECTCOLUMNS(
                Contact,
                "Street", Contact[MailingStreet],
                "City", Contact[MailingCity],
                "State", Contact[MailingState],
                "Zip", Contact[MailingPostalCode]
            ),
            SELECTCOLUMNS(
                Contact,
                "Street", Contact[Home_Address__Street__s],
                "City", Contact[Home_Address__City__s],
                "State", Contact[Home_Address__StateCode__s],
                "Zip", Contact[Home_Address__PostalCode__s]
            )
        )
"if statement"
DAX
  • johnt75's avatar
    johnt75
    2 years ago

    Try

    EVALUATE
SELECTCOLUMNS (
    Contact,
    Contact[FirstName],
    Contact[LastName],
    Contact[Christmas_Gift__c],
    "Street", COALESCE ( Contact[Home_Address__Street__s], Contact[MailingStreet] ),
    "City", COALESCE ( Contact[Home_Address__City__s], Contact[MailingCity] ),
    "State", COALESCE ( Contact[Home_Address__StateCode__s], Contact[MailingState] ),
    "Zip", COALESCE ( Contact[Home_Address__PostalCode__s], Contact[MailingPostalCode] )
)
  • johnt75's avatar
    johnt75
    2 years ago

    You need the name of the column before the IF statement

    EVALUATE
SELECTCOLUMNS (
    Contact,
    Contact[FirstName],
    Contact[LastName],
    Contact[Christmas_Gift__c],
    "Street",
        IF (
            ISBLANK ( Contact[MailingCity] ),
            Contact[Home_Address__Street__s],
            Contact[MailingStreet]
        ),
    "City", COALESCE ( Contact[Home_Address__City__s], Contact[MailingCity] ),
    "State", COALESCE ( Contact[Home_Address__StateCode__s], Contact[MailingState] ),
    "Zip", COALESCE ( Contact[Home_Address__PostalCode__s], Contact[MailingPostalCode] )
)

9 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    2 years ago

    Try

    EVALUATE
SELECTCOLUMNS (
    Contact,
    Contact[FirstName],
    Contact[LastName],
    Contact[Christmas_Gift__c],
    "Street", COALESCE ( Contact[Home_Address__Street__s], Contact[MailingStreet] ),
    "City", COALESCE ( Contact[Home_Address__City__s], Contact[MailingCity] ),
    "State", COALESCE ( Contact[Home_Address__StateCode__s], Contact[MailingState] ),
    "Zip", COALESCE ( Contact[Home_Address__PostalCode__s], Contact[MailingPostalCode] )
)
    • djerryanderson's avatar
      djerryanderson
      Regular Visitor
      2 years ago

      I think I spoke too soon. That works...if there is no partial address information filled in. The main issue with this solution is that it's not all of the HOME address or all of the BUSINESS address. It could conceivaly piece together parts of both if, for example, the home street is there but the home city is missing. In that case the street is OK but the city is incorrect.

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        2 years ago

        If you wanted to base the test for each column on just the city you could replace the COALESCE statements with 

        IF( ISBLANK( Contact[Home_Address_City_s]), Contact[MailingStreet], Contact[Home_Address_Street])

         

    • djerryanderson's avatar
      djerryanderson
      Regular Visitor
      2 years ago

      Here are 2 screen snips of the input and expected output. Input has a contact with a home address and business address. Either one or both could have values. I want the HOME address to be the one populated if it is present. If not, use the business adress. 

       

      InputOutput