Forum Discussion

jgorigo's avatar
jgorigo
Regular Visitor
4 years ago
Solved

Lookupvalue DAX with a table multiples values

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's avatar
    Barthel
    4 years ago

    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

     

2 Replies

  • Barthel's avatar
    Barthel
    Solution Sage
    4 years ago

    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's avatar
      jgorigo
      Regular Visitor
      4 years ago

      Hi.

      Now it works perfec. Thank so much!