topic Re: Excel vlookup formula to Powerbi in DAX Commands and Tips

Excel vlookup formula to Powerbi

Anonymous — Wed, 21 Sep 2022 13:18:28 GMT

Hi,
I need to convert the Excel vlookup to Dax - VLOOKUP(@BO:BO,'Transit Time query'!A:D,4,FALSE)

i have joined Route column in BO Sheet with Transit Time query sheet -Route column and now i am not able to figure out - 'Transit Time query'!A:D,4,FALSE) how do i convert this to Dax column ? fianl output as Transit time (3,4, 10,60,80... )
pls check the screenshots attached below

Final Output -

BO Column Name - Route

Transit Time Query Sheet -

Re: Excel vlookup formula to Powerbi

Greg_Deckler — Wed, 21 Sep 2022 13:20:30 GMT

Anonymous There is a LOOKUPVALUE function in DAX although I find that MAXX(FILTER(...),...) tends to be more reliable.

Re: Excel vlookup formula to Powerbi

Anonymous — Wed, 21 Sep 2022 13:28:22 GMT

Can you please provide the full syntax for this Vlookup ?
That will be more helpful

Re: Excel vlookup formula to Powerbi

AnthonyJoseph — Wed, 21 Sep 2022 19:14:02 GMT

Hi Anonymous

can you use the below:

calculated column = Lookupvalue(Transit Time Query Sheet[Transit time],Transit Time Query Sheet[Route],Transit Time Query Sheet[Route])

There are many articles about Lookupvalue, you can give a read:

https://www.sqlbi.com/articles/introducing-lookupvalue/

Hope this helps

Thanks,

AnthonyJoseph

Re: Excel vlookup formula to Powerbi

v-yueyunzh-msft — Thu, 22 Sep 2022 03:46:01 GMT

Hi, Anonymous

According to your description, you want to VLOOKUP [TransitTime] in 'Transit Time Query' table to the [Transit Time] in 'BO Column Name' table. Right?

You can click "New Column" in 'BO Column Name' table to create a calculated column:

Transit Time=LOOKUPVALUE('Transit Time Query'[TransitTime],'Transit Time Query'[Route],'BO Column Name'[Route])

You can refer to this document:

LOOKUPVALUE function (DAX) - DAX | Microsoft Learn

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: Excel vlookup formula to Powerbi

Anonymous — Thu, 22 Sep 2022 05:29:10 GMT

Hi AnthonyJoseph,
i am getting this error in calculated column, Pleae see the screenshot attached

Re: Excel vlookup formula to Powerbi

v-yueyunzh-msft — Thu, 22 Sep 2022 05:38:56 GMT

Hi, Anonymous

The third parameter is wrong, you can try to replace it to the 'BO Column Name'[Route].

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: Excel vlookup formula to Powerbi

Anonymous — Thu, 22 Sep 2022 05:42:36 GMT

Hi,
included 3rd parameter but still getting error as below

Re: Excel vlookup formula to Powerbi

v-yueyunzh-msft — Thu, 22 Sep 2022 06:04:17 GMT

Hi , Anonymous

This error occurs because that there are repeate data in 'Transit Time Query'[TransitTime],like this:

You can try to user this dax to fix it:

Transit Time = CALCULATE(FIRSTNONBLANK('Transit Time Query'[TransitTime],TRUE()), FILTER('Transit Time Query','Transit Time Query'[Route]='BO Column Name'[Route] ) )

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly