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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

LookupValue not returning all values

I am at a loss and have dug through Google looking for something similiar.

 

I used this formula to create a column

 

FY22%Comp = MROUND( IF(Preconstruction[TaskStartDate]>DATE(2022,06,30),0,if(DATE(2022,06,30)>Preconstruction[TaskFinishDate],1,DATEDIFF(Preconstruction[TaskStartDate],DATE(2022,06,30),MONTH)/Preconstruction[Precon Duration])),.10)
 
This gives me a % complete for a give projects and rounds it to the nearest 10% 
 
From there I created a custom table that has a column for % complete so that I can run a LOOKUPVALUE formula between the two tables.
 
It works perfect except for 30% 60% and 70%
 
There is no direct relationship between these 2 tables.
 
Here is my lookup value formula
 
FY23%SpendPC = LOOKUPVALUE(Curves[Back Loaded Current],Curves[Percent Complete],Preconstruction[FY23%Comp])
 
It works for every single % except for the 3 mentioned above. I have pulled the values out of both tables and dropped them in Excel to verify they indeed match and they do.
 
Ccal87_0-1673373261646.png

 

 

Has anyone ever had this happen, or is something off with my formula?

1 REPLY 1
AlexisOlson
Super User
Super User

I'd guess there isn't a single unique match (i.e. zero or multiple matches) or possibly a floating point error issue.

 

Without actually looking at the tables and preferably the pbix, it's difficult to say for sure. Can you share a dummy file that reproduces this issue?

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.