Lookupvalue DAX with a table multiples values

[jgorigo]

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.

[Barthel · Accepted solution]

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

[jgorigo]

Hi.

Now it works perfec. Thank so much!