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 _...
  • 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

     

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