cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
djohnson
Advocate I
Advocate I

BUG? LOOKUPVALUE() limited to 5,999 matches

Background: Trying to fill-in ID values from a table [approx 11k rows] with another table [approx 11k rows] using LOOKUPVALUE(). Upon checking the results I noticed there were roughly half missing values (that I knew existed). I created the relationship between the two tables (1-1) and rechecked: still missing matches. When I looked at the exact count of the matches, it was 5,999 - very peculiar. It looks like DAX has a limit on LOOKUPVALUE() for a table - which is disturbing. I was able to switch over to using the RELATED() function since I had a relationship between the two tables.

 

Comments? 

2 REPLIES 2
AWolvan9
Regular Visitor

I had the same issue, big table (2 million rows), lookup value on 3 columns just didnt seem to be working. Tested it other way ( i.e. table 1 looking up table 2 rather than other way around) and lookup was able to pull values. 
Going to try creating a unique ID column, creating a relationship and using the related funciton instead. 

Edit: Made the relationship based on a unique col, and a related function did work, but interestingly the relationship also made the lookupvalue work. Lesson to learn? make a unique col and make a relationship?

This basically makes lookupvalue uesless though!!!

Ciria
Advocate III
Advocate III

Hi djohnson:

 

I was also working with that formula but for a different goal.

 

Seeing what you say I think that creating a relationship and Related formula should be enough to match both tables and get what you want.

 

If you cannot use Relate for a problem with the relationship and the Cross filter direction maybe you can use "USERELATIONSHIP" sometimes it helps to calculate something when it is not fully connected in both sides.

 

Good luck!

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors