LOOKUPVALUE() return first of multiple matches

[Anonymous]

Hi Community

I am trying to return an email address in a contacts table asscoiated with the customerID in an Invoices table. 

Contacts Table relevant fields: _parentcustomerid_value, emailaddress1

Invoices Table relevant fields: _customerid_value

DAX formula: 

lookupvalue(contacts[emailaddress1],contacts[_parentcustomerid_value],invoices[_customerid_value])

The problem i have is there can be more than one contact for each _parentcustomerid_value, so i get the "A table of multiple values..." error. 

So I need to return either the first matching record OR i can be more specific by using an extra field in criteria Contacts[whe_Guid] IS NOT BLANK but how do i incorporate not blank in the lookup criteria expression?

I have tried many variations of calculate(), firstnoblank(), min() etc but either get an error or a blank return value.

[Dr_T2003]

This worked perfectly, thanks!

[Elipum]

This may be an easier alternative

MAXX (
    FILTER ( SearchTable, SearchTable[Product] = ThisTable[Product] ),
    SearchTable[Category]
)

[kcantor]

@Anonymous 

This seems like something I would solve in Power Query with a merge between the tables. The merge would keep all of the emails, not just the first. You could then filter the data within the table.

[Anonymous]

Hi @kcantor 

Not sure that is right.

If my invoice table has a record for CompanyA but Contatcs has 3 records for CompnayA, i would end up with 3 records in the merged table and still the same problem retrieving the relevant record value to return in a lookupvalue()

[kcantor]

@Anonymous 

You would need to clean up the data in the table instead of in the LookUpValue which would make the dax easier. How do you currently determine which value to keep? This really seems like a source table issue.

[Anonymous]

Hi @kcantor 

Perhaps a little context will answer that.

The tables are in a Dynamics CRM. The invoices are generated in a separte ERP system and synched across. There is a GUID on the record from the ERP that matches a company record in CRM. That gets the Invoice tied to the Comapny. However to then contact the Company we need to get an email address from 1 or more contacts. So we do not  need to determine which value to keep? 

I am awaiting on my IT people to identify if/how the GUID on the invoice matches.

[MitchM · Accepted solution]

@Anonymous is there a reason you can not link the two tables in the data model? If you have guid's in each table that can form a relationship you can then:

a) If you want the first email match to show in the same table as the invoice just drag the email column to the table and select "first" from the values

b) If you want to see all the emails you can creat a seperate email table that will filter to the appropritate emails when an invoice is selected.

Appologies if this misses the mark

[Anonymous]

hi @MitchM 

Having a look at your suggestions.

However this will still fall over at LOOKUPVALUE() if there are multiple values returned.

[MitchM]

My suggestion would not need the use of lookup or any measure or calc. column. That's why I was not sure if it was a good fit for what you were looking for. Happy to help further if this does not work.

[Anonymous]

hi @MitchM 

Applogies for delay, had a holiday 🙂

Your suggestion works in a table visual just fine, however I need that 'First' email as a column in a table. So I need to return either the first matching record.

I have found a solution now - 

CALCULATE(FIRSTNONBLANK(contacts[emailaddress1],1),FILTER(contacts,contacts[_parentcustomerid_value]=invoices[_customerid_value] ))