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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

LOOKUPVALUE() return first of multiple matches

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.

1 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

View solution in original post

10 REPLIES 10
Dr_T2003
New Member

This worked perfectly, thanks!

Elipum
New Member

This may be an easier alternative

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

@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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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

@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.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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.

 

@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
Not applicable

hi @MitchM 

Having a look at your suggestions.

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

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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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