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 LOOK...
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

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

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

      Ronie

       

      I didn’t find any issue about the solution provided by Jahida before. I also tried with similar tables and the LOOKUPVALUE function worked well. If possible, could you please provide your PBIX file for troubleshooting?

       

      Best Regards,

      Herbert

      • Ronie's avatar
        Ronie
        Frequent Visitor
        10 years ago

        Hello Herbert, 

        I'm extremely sorry for the delay in my reply, I was off sick with a nasty cold (just when it gets sunny in London, the irony!).

        Of course! I need to remove some details as the file includes some  sensitive information. I'll share the file sometime this afternoon, as soon as I get a chance.

        Thanks a lot for your help!

        -Ronie