Forum Discussion
LOOKUPVALUE returns blank value
Hi Paolo750f,
I modified your formula and it seems work on my side with your sample data, you can try to use below formula if it suitable for your requirement:
Week = LOOKUPVALUE(DateKey[Week],DateKey[Date],DATEVALUE(leads[new_enquirydate]))
Regards,
Xiaoxin Sheng
Hi Paolo750f,
>>Following an online guide i created a date table called DateKey per below and created a relationship between DateKey[date] and Enquiries[enquiry date].
If these table contains the relationship, you can directly use RELATED('DateKey'[Week]) to get the related week value.
In addition, if these tables not contains the relationship, you can use lookupvalue function, but you should ensure this function can find out the unique value.
Regards,
Xiaoxin Sheng
Hi Anonymous
thanks for your clarification.
The tables are related as follows: Leads[enquiry date] to Datekey[Date], many to one, Single, Active.
I tried using RELATED as you suggested however i do not get anything returned in the 'week number' column in my table (see shot below):
If i remove the relationship and try using LOOKUP i get the same result:
I've tried returning other values in the Datekey table with the same result.
I've recreated the datekey table in a new instance of Power BI desktop, created another new table with a column using LOOKUPVALUE and it works however, when i load my 'leads' table and try to do the lookup from either the enquiry date or createdon date it does not return anything.
Am i missing someting obvious here?
Thanks,
Paul
Hey Paolo750f, a couple questions here:
- Have you made sure that both the date columns are in the Date format?
- Is there a reason you need to use lookupvalue? If these two tables are related, you can use fields from both in the same chart. You could bring the Week value from the Date table on the chart, and then bring on the field from the Leads table to see them in the same chart.
I may not understand what you are trying to accomplish, so please feel free to correct my understanding!
Hi Jared,
Yes, both the date columns are in the same date format.
I was using lookupvalue following an article i found. I tried making the tables related and doing what you suggested but i don't get the result i'm expecting and if i try creating a new column using the RELATED expression the column remains blank.
What i'm trying to achieve is to be able to use the date table to filter my leads and opportunities by week (or any other range configured in the date table such as week of financial year, quarter, or financial year).
Strangely, if i create a new table with a column containing some dates and create a new column with lookupvalue to my date table it works correctly but whenever i try exactly the same using the data from CRM it doesn't return anything. I am wondering of there is something strange with my data as even if export from CRM to Excel and import that as a data source it does not work.
What i'm doing should work but for some reason it doesn't with my data set. I've even tried starting again and creating a new data set fromCRM and a new date table but get same result.
Paul
Hi Paolo750f,
Can you upload the pbix file and share us the link ?(1dr, google) I will test on it.
Regards,
Xiaoxin Sheng