Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
djerryanderson
Regular Visitor

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]
            )
        )
2 ACCEPTED SOLUTIONS
johnt75
Super User
Super User

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

View solution in original post

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

View solution in original post

9 REPLIES 9
johnt75
Super User
Super User

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

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.

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

 

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

djerryanderson_0-1730729099515.png

 

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

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! 🙂

Thank you!!! That worked perfectly. 

adudani
Super User
Super User

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,

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

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. 

 

InputInputOutputOutput

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.