IF THEN to select particular columns

[djerryanderson]

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]

            )

        )

[johnt75 · Accepted solution]

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 · Accepted solution]

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]

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]

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]

I'm probably just butchering the syntax...but I tried the IF statement as shown above to be all inclusive. I did change the = BLANK to ISBLANK after some googling, it would produce no errors but said "end of input"  and yielded no results.

Now I've tried the solution you suggested and I get "Parameter is not the correct type"

// Learn more about DAX queries at https://aka.ms/dax-queries

[johnt75 · Accepted solution]

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] )
)

[djerryanderson]

OK that works. Thanks again johnt75. Done a bit of SSRS but just started Power BI last week. Not very good at either but thankfully we have tech forums! 🙂

[djerryanderson]

Thank you!!! That worked perfectly. 

[adudani]

hi @djerryanderson ,

Kindly provide the sample input/output in a usable format (excel, csv, table, link to pbix etc.) masking sensitive information.

reference : https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447... 

Thanks,

[djerryanderson]

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