LOOKUPVALUE returns blank value

[Paolo750f]

Hi,

I'm pretty new to Power BI but have suddenly become my companies 'expert' 😉

I'm being asked to produce a chart to show 'Enquiries' by week. This could be week YTD or Fiscal week.  Following an online guide i created a date table called DateKey per below and created a relationship between DateKey[date] and Enquiries[enquiry date].  The DateKey[date] column is in the same format as Enquiries[enquiry date] that i want match.

I have been trying to look up the 'week' value based on the row where the Enquiries[enquiry date] matches the 'Date' in the DateKey table.  I created a calculated column with the following DAX: Week number = LOOKUPVALUE('DateKey'[Week],'DateKey'[Date], 'leads'[enquiry date])

This doesn't return anything in the column i created.  I've spent hours looking at articles about how to use LOOKUPVALUE and my syntax looks correct with no errors.

I'm sure i'm missing something simple here and appreciate any support and guidance 🙂

[Anth79]

I'm getting an error with something similar, I know its probably me just missing something simple:

Opportunity FY = isblank(if( LOOKUPVALUE('FWS Receipts Household'[Opportunity Start Date], 'FWS Receipts Household'[Supporter URN], 'FWS Receipts Supporters'[Owning Party URN]), 'FWS Receipts Supporters'[Opportunity Start Date], LOOKUPVALUE('FWS Receipts Household'[Opportunity Start Date], 'FWS Receipts Household'[Supporter URN], 'FWS Receipts Supporters'[Owning Party URN])))

I'm trying to pull through the start date realting to the Household a Supporter belongs to, when the Supporter has a blank start date, but get the following

ERROR: A table of multiple values was supplied where a single value was expected.

[chbraun]

Hi Paolo,

I am late to the conversation and I might be missing something here, but I don't quite understand why you are looking into LOOKUPVALUE for creating a chart showing enquiries by week. Wouldn't this approach work:

+ create measure to count enquiries, something like enquiries = DISTINCTCOUNT(EnquiryID)

+ make sure that the Enquiries table and the date table have a relationship

+ create a chart (for example bar chart) with week number as x-axis and enquiries metric as y-axis

Et volia - number of enquiries per week. 🙂

Am I missing something here or is this what you are after?

Cheers,

Christian

[Paolo750f]

Hi Christian,

Your suggestion was how i expected things to work but when i try this i don't get the expected result.  below are a couple of screen shots to show what happens.

When i add 'week' to x axis this is what i get:

[v-shex-msft]

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

[Paolo750f]

Hi @v-shex-msft

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

[Anonymous]

Hey @Paolo750f, a couple questions here:

1. Have you made sure that both the date columns are in the Date format?
2. 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!

[Paolo750f]

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

[v-shex-msft]

Hi @Paolo750f,

Can you upload the pbix file and share us the link ?(1dr, google) I will test on it.

Regards,

Xiaoxin Sheng

[Paolo750f]

Hi Xiaoxin,

i've cretaed a cutdown version of the file and put it here https://onedrive.live.com/?id=11887C660FAAD99%21205&cid=011887C660FAAD99

I've tested it and i see the same issues.  I'd be interested to see if you find anything.

Regards,

Paul

[v-shex-msft · Accepted solution]

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

[ParthaD]

Thank you for supporting us with your experiance and knowledge, today(16-12-2022), i was also stuggling to get the resultant week no, but, post reading you blog, i am able to fix this for my organisation. Thank you again Xiaoxin Sheng.

[Paolo750f]

Hi Xiaoxin,

Your formula works great, thank you for your help 🙂

Regards,

Paul