Forum Discussion

ahumke's avatar
ahumke
Icon for Helper I rankHelper I
7 years ago
Solved

DAX table syntax to select max year and associated values within categories

I'm trying to create a new table using DAX that will select the max year and an associated value (KPI weighted score component) of that year within three categories. (area, program title, and KPI me...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi ahumke ,

     

    Make a little modification to above DAX formula.

    ProgramSummary =
ADDCOLUMNS (
    GROUPBY (
        'SummaryKPI',
        'SummaryKPI'[Area],
        'SummaryKPI'[Program Title],
        'SummaryKPI'[KPI Metric],
        "MAXYear", MAXX ( CURRENTGROUP (), 'SummaryKPI'[JanuaryDate] )
    ),
    "Score", LOOKUPVALUE (
        SummaryKPI[KPI Weighted Score Component],
        SummaryKPI[Area], [Area],
        SummaryKPI[Program Title], [Program Title],
        SummaryKPI[KPI Metric], [KPI Metric],
        SummaryKPI[JanuaryDate].[Date], [MAXYear]
    )
)

     

    Best regards,

    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

Hi ahumke ,

 

Make a little modification to above DAX formula.

ProgramSummary =
ADDCOLUMNS (
    GROUPBY (
        'SummaryKPI',
        'SummaryKPI'[Area],
        'SummaryKPI'[Program Title],
        'SummaryKPI'[KPI Metric],
        "MAXYear", MAXX ( CURRENTGROUP (), 'SummaryKPI'[JanuaryDate] )
    ),
    "Score", LOOKUPVALUE (
        SummaryKPI[KPI Weighted Score Component],
        SummaryKPI[Area], [Area],
        SummaryKPI[Program Title], [Program Title],
        SummaryKPI[KPI Metric], [KPI Metric],
        SummaryKPI[JanuaryDate].[Date], [MAXYear]
    )
)

 

Best regards,

Yuliana Gu

  • ahumke's avatar
    ahumke
    Icon for Helper I rankHelper I
    7 years ago

    Thank you!  that worked perfectly!