Forum Discussion

Paolo750f's avatar
Paolo750f
Frequent Visitor
9 years ago
Solved

LOOKUPVALUE returns blank value

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

  • Anonymous's avatar
    Anonymous
    9 years ago

    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

12 Replies

  • Anth79's avatar
    Anth79
    Advocate I
    6 years ago

    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.
  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      Paolo750f
      Frequent Visitor
      9 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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!

  • chbraun's avatar
    chbraun
    Helper I
    9 years ago

    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's avatar
      Paolo750f
      Frequent Visitor
      9 years ago

      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: