Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago

DAX Retrieve Year Value in a Virtual Table

Hello everyone

I have a table with values to display in a Segmenter and another table with target values.

Depending on the selected year, only the values q appear in the T_Objetivo will be shown and if there is no record for that year, all the records of the T_DiasCierre are shown.

That seems to more or less work for me.

I have the problem when trying to retrieve the year value (SELECTEDVALUE(Calendar[Date].[ Year])) that does not retrieve any value.

finalCalc =

var anno = SELECTEDVALUE(Calendar[Date].[ Year])

WHERE _Filtered =

CALCULATE(SUM(T_Objetivo[VALOR]),T_Objetivo[AÑO] = anno && T_Objetivo[AMBITO] = "USER" && T_Objetivo[INDICADOR] = "CLOSED" )

VAR calculation =

ADDCOLUMNS (

GROUPBY (

T_U_DiasCierre,

T_U_DiasCierre[Id],

"Value", SUMX ( CURRENTGROUP (), [Value] )

),

"Active", IF(ISBLANK( _Filtered), 1,IF ( [Value] = _Filtered, 1 ))

,"ANNO", SELECTEDVALUE(Calendar[Date].[ Year])

,"a_calen",year

)

return calculo

What can I do to solve this problem?

Thanks a lot

6 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    SELECTEDVALUE from where?  Do you have a visual where the calendar date is used together with the measure?  If not then you may not have the right (or no) filter context.

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    3 years ago

    The year value is taken from a slicer in the Calendar table

    With the selected year you must create a new segmenter with only the active values

    The year I am trying to retrieve with SELECTEDVALUE(Calendar[Date]. [Year]) but when you create the new virtual table you are not retrieving the selected year

    finalCalc =
    var anno = SELECTEDVALUE(Calendar[Date].[ Year])
    VAR _Filtered =
    CALCULATE(SUM(T_Objetivo[VALUE]),T_Objetivo[YEAR] = anno && T_Objetivo[SCOPE] = "USER" && T_Objetivo[INDICATOR] = "CLOSED" )
    VAR calculation =
    ADDCOLUMNS (
    GROUPBY (
    T_U_DiasCierre,
    T_U_DiasCierre[id],
    "Value", SUMX ( CURRENTGROUP(), [Value] )
    ),
    "Active", IF(ISBLANK( _Filtered), 1,IF ( [Value] = _Filtered, 1 ))
    ,"ANNO", SELECTEDVALUE(Calendar[Date].[ Year])
    ,"a_calen",anno
    )
    return calculo

    The result of the T_finalCal will be mostrarT_DiasCierre and a 1 will appear in the Active field if the value is in T_Objetivo

    This is the initial approach, but I can modify it if someone gives me a good result


    • lbendlin's avatar
      lbendlin
      Super User
      3 years ago

      If there is no single year selected  SELECTEDVALUE will return BLANK()

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    3 years ago

    I think the most logical thing would be that if we do not have a selected year we take the biggest
    SELECTEDVALUE(Calendar[Date]. [Year],MAX(CALENDAR[year]))

    • lbendlin's avatar
      lbendlin
      Super User
      3 years ago

      Then do that.  MAX() is a good stand-in for SELECTEDVALUE() especially when the expected Total is also the last value.

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    3 years ago

    I have a Calendar table and from this table we show a filter with the different years that can be selected.
    When you select a year, I'm trying to retrieve it with SELECTEDVALUE(Calendar[Date]. [Year],MAX(CALENDAR[year])), but in the virtual table does not retrieve any value for the year
    I do not know if the virtual tables can not use values that come from some filter