Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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
Hi.
Now it works perfec. Thank so much!