Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape 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.
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.
Solved! Go to 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] )
)
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] )
)
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"
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.
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,
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.
User | Count |
---|---|
25 | |
21 | |
20 | |
13 | |
13 |
User | Count |
---|---|
40 | |
28 | |
28 | |
22 | |
21 |