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

 

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

     

     

    • v-haibl-msft's avatar
      v-haibl-msft
      Microsoft Employee
      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-msft

        Thank you very much! Works like a charm!