topic Re: IF THEN to select particular columns in DAX Commands and Tips

IF THEN to select particular columns

djerryanderson — Fri, 01 Nov 2024 14:59:59 GMT

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]

)

Re: IF THEN to select particular columns

adudani — Fri, 01 Nov 2024 15:30:20 GMT

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/1447523#M607150

Thanks,

Re: IF THEN to select particular columns

djerryanderson — Fri, 01 Nov 2024 15:54:19 GMT

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.

Re: IF THEN to select particular columns

johnt75 — Fri, 01 Nov 2024 16:00:11 GMT

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

Re: IF THEN to select particular columns

djerryanderson — Fri, 01 Nov 2024 17:18:12 GMT

Thank you!!! That worked perfectly.

Re: IF THEN to select particular columns

djerryanderson — Fri, 01 Nov 2024 18:27:02 GMT

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.

Re: IF THEN to select particular columns

johnt75 — Mon, 04 Nov 2024 11:31:11 GMT

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

Re: IF THEN to select particular columns

djerryanderson — Mon, 04 Nov 2024 14:05:43 GMT

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

Re: IF THEN to select particular columns

johnt75 — Mon, 04 Nov 2024 14:29:12 GMT

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

Re: IF THEN to select particular columns

djerryanderson — Mon, 04 Nov 2024 14:36:00 GMT

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