topic Re: Lookupvalue DAX with a table multiples values in DAX Commands and Tips

Lookupvalue DAX with a table multiples values

jgorigo — Mon, 01 Aug 2022 13:20:15 GMT

Hello everyone. I'll really appreacite your help.

I have a table named uf_prosp with data for bussiness day (_plazo) and interest rates (_tasas) for every end of month (_fecha_curva).

The column _fecha_curva has data for 12/30/2021, 01/31/2022 and 02/28/2022.

How can i say to this measure to look for to VAR y1 and VAR y2 accordind to date(2021,12,30)

FD =
VAR fecha_curva = date(2021,12,30)

VAR x1 = 1

VAR x2 = 15
VAR y1 = LOOKUPVALUE(uf_prosp[_tasas],uf_prosp[_plazo],x1)
VAR y2 = LOOKUPVALUE(uf_prosp[_tasas],uf_prosp[_plazo],x2)

RETURN y1 * y2

I get this error message

Thanks.

Re: Lookupvalue DAX with a table multiples values

Barthel — Mon, 01 Aug 2022 14:10:45 GMT

Hey,

The LOOKUPVALUE does not take the date into account, so there are multiple results (3 in total, 1 for each date). This is not allowed. A measure can only return 1 value. You need to add a filter context for the correct date. This is best done with a CALCULATE function. Always try to work with CALCUATE as much as possible and avoid LOOKUPVALUE, this is best practice.

FD = VAR fecha_curva = DATE ( 2021, 12, 30 ) VAR x1 = 1 VAR x2 = 15 VAR y1 = CALCULATE ( SELECTEDVALUE ( uf_prosp[_tasas] ), KEEPFILTERS ( uf_prosp[_plazo] = x1 ), KEEPFILTERS ( uf_prosp[fecha_curva] = fecha_curva ) ) VAR y2 = CALCULATE ( SELECTEDVALUE ( uf_prosp[_tasas] ), KEEPFILTERS ( uf_prosp[_plazo] = x2 ), KEEPFILTERS ( uf_prosp[fecha_curva] = fecha_curva ) ) RETURN y1 * y2

Re: Lookupvalue DAX with a table multiples values

jgorigo — Mon, 01 Aug 2022 14:34:37 GMT

Hi.

Now it works perfec. Thank so much!