Forum Discussion

Ronie's avatar
Ronie
Frequent Visitor
10 years ago
Solved

LOOKUPVALUE returns blank value

Hello, 
Following the recent help of Jahida (thanks again!), I managed to do some good progress with the dashboard I'm preparing for my boss. Unfortunately, I'm facing a new problem, related to LOOKUPVALUE that I didn't manage to solve.

Context
Previously, I needed to convert the time to BST in my 'Yearly'  table, during summer time only. I followed the solution provided here to create a Adjusted time column, [Time (Adj)].

Time(Adj) = IF(RELATED('Date'[IsBST]), Yearly[Time] + 1/24, Yearly[Time])

I'm mentioning it as I feel it might be related to my new issue somehow.

 

Now, I want to measure the contact volume per shift.


Approach
To that end, I created a calculated column 'Yearly'[Shift] in my 'Yearly' table, to display the shift corresponding to the 'Yearly''[Time (Adj)] time, based on my 'Time' table.

 

Shift = Lookupvalue(Time[Shift],'Time'[Time],Yearly[Time(Adj)])

For what I see, it works unless when the 2 conditions below are met:

- the value in the 'Yearly'[Time] Colum is  ranging between 23:00 and 23:59, Yearly'[Shift]
- the date is BST, e.g. 'Yearly'[Time] and 'Yearly'[Time(adj)] don't match

 

Any idea of what could be causing the issue?

Tables and relationship
Time table - https://www.imageupload.co.uk/images/2016/08/17/TimeTable.jpg
Yearly Table - https://www.imageupload.co.uk/images/2016/08/17/YearlyTable.jpg
Relationship - https://www.imageupload.co.uk/images/2016/08/17/Relationship.jpg


Troubleshouting
I checked other posts and I tried the following:
- remove the relationship between 'TIme' and 'Yearly' Table
- use the DAX Time function when refering to time
- use relate instead of lookupvalue (when the relationship is active)


Thank you very much for your time and your help!

-Ronie

Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    10 years ago

    Ronie

     

    Please create a new column in Time table with following formual.

    Time_Format =
FORMAT ( 'Time'[Time], "Long Time" )

    Then update the Shift column formula as below. It should can resolve the problem.

    Shift = 
 LOOKUPVALUE ( Time[Shift], 'Time'[Time_Format], FORMAT( Yearly[Time(Adj)], "Long Time" ) )

    The root cause is that some date of Time(Adj) are 12/31 but all date in Time(Time) are 12/30.

     

    Best Regards,

    Herbert

8 Replies

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    10 years ago

    Ronie

     

    For those blank Shift values, could you please confirm the value of Time(Adj) exists in Time[Time]? The LOOKUPVALUE function will only return BLANK when there is no match that satisfies all the search values.

     

    For example, as your screenshot below, check if 00:20:00 exist in Time[Time] column.

     

    Best Regards,

    Herbert

    • Ronie's avatar
      Ronie
      Frequent Visitor
      10 years ago

      v-haibl-msftThank you very much for coming back to me on this! That's something I checked, along with possible duplicates. It's not the cause of the issue.

      Incidently, I eventually found a solution this morning. I confirmed the issue was coming from the Time(adj) by testing the vlookupvalue fonction on the non adjusted time column. 

      I have no idea why this works but here what I did:

       

      I replaced

       

      Shift = Lookupvalue(Time[Shift],'Time'[Time],Yearly[Time(Adj)])


      by

      Shift = Lookupvalue(Time[Shift],Time[Time],TIME(HOUR(Yearly[Time(Adj)]),MINUTE(Yearly[Time(Adj)]),SECOND(Yearly[Time(Adj)])))

      -Ronie

      • Ronie's avatar
        Ronie
        Frequent Visitor
        10 years ago

        Actually, it didn't work... I simply got less blank :(