topic Re: Xlookup with not exact values in power bi in DAX Commands and Tips

Xlookup with not exact values in power bi

LiMa92 — Wed, 31 Aug 2022 12:12:33 GMT

Hi,

I´ve got a problem in power bi that i´ve been unable to find an answer to or solve yet.

So what we have is one excel file and a table from a SQL server with 1 table in each that I have imported in Power BI

The first table have a column with a unique ID and a column with unique date and time.

each rows represent a product with manufacturing data for that specific product.

Table looks like this:

Product ID | Completed time

1 2022-08-31 06:00

2 2022-08-31 06:02

3 2022-08-31 06:05

and so on

In the Second table we have a list of when a specific parameter is changed

in this table we have a column for date and time and a column with the changed value.

looking like this:

What i want to do is to add a column to the first table and put the changed value from the Second table and put that for each unique product that is produced after the changed parameter.

the table should look something like this like this:

Product ID | Completed time | parameter from second table

1 2022-08-31 06:00 1,85

2 2022-08-31 06:02 1,85

3 2022-08-31 06:05 1,85

In Excel I would use the Xlookup function to get this to work: =XLOOKUP(I2;$A$2:$A$30;$B$2:$B$30;;-1)

In excel it would look like this:

Table with parameter change:

Table with unique products and Xlookup value

I would like to do this directly in Power Bi so we don't get another excel table that we need to keep updated.

Thanks in Advance!

Re: Xlookup with not exact values in power bi

v-yadongf-msft — Fri, 02 Sep 2022 08:22:21 GMT

Hi LiMa92 ,

This is table1:

This is table2:

Please create a new column in table2:

Column = LOOKUPVALUE('table1'[value],'table1'[Date],'table2'[Date])

If I misunderstood your needs, please contact us in time.

Best regards,

Yadong Fang

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

Re: Xlookup with not exact values in power bi

LiMa92 — Mon, 12 Sep 2022 06:13:52 GMT

Hi Yadong,

is it possible to somhow do this when the date and time is not matching exactly in the 2 tables?

in youre example:

I would like to make table 2 check the date in table 1 and if it´s not exactly matching then take the value from the closest older date and use that value instead.

Best Regards LM