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

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

      • Ronie's avatar
        Ronie
        Frequent Visitor
        10 years ago

        Alright!
        Sorry Herbert, I had to remove a lot of things from the file and which was breaking it, so I took me a bit longer than expected.

         

        You can find the pbix here:
        https://1drv.ms/f/s!AtMrshF_PQK9gRFxCHmOA3Xl3TJP

         

        The table will probably look empty at first as I didn't clean the file properly. But if you right click on [Time] in the 'Yearly' table and sort descending, you should be able to see the issue I described in the column [Shift].


        Thanks again for your help!

        -Ronie