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

[ahumke]

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 metric).  I can get the table to generate with the statement below but the "KPI Weghted Score Component" is not returning the expected value associated with that max year (it returns the max within the five years listed).  How do I fix this?

ProgramSummary = GROUPBY('SummaryKPI', 'SummaryKPI'[Area], 'SummaryKPI'[Program Title], 'SummaryKPI'[KPI Metric], "MAXYear", maxx(CURRENTGROUP(), 'SummaryKPI'[JanuaryDate]),"Score", maxx(CURRENTGROUP(),'SummaryKPI'[KPI Weighted Score Component]))

Sample Link

[v-yulgu-msft · Accepted solution]

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]

Thank you!  that worked perfectly!

[HotChilli]

I'm not an expert in the GROUPBY function but it does look like any reference to CURRENTGROUP relates to the group created before you start using the CURRENTGROUP() function.  There may be a way to say 'get me the field next to the max [January Date]' but I don't know how to do that.

However, it looks like you could create a calculated column and use LOOKUPVALUE e.g.

Column1 = LOOKUPVALUE(SummaryKPI[KPI Weighted Score Component],  SummaryKPI[Area], ProgramSummary[SummaryKPI_Area], SummaryKPI[Program Title], ProgramSummary[SummaryKPI_Program Title], SummaryKPI[KPI Metric], ProgramSummary[SummaryKPI_KPI Metric], SummaryKPI[JanuaryDate], ProgramSummary[MAXYear] )

[ahumke]

I am getting an error on that formula that "A single value for column 'summaryKPI_Area' cannot be determined. While using that code...      

[HotChilli]

OK, you are going to have to debug this. 

I used the data you provided. I used 'Get Data' to pull the data from Excel.  I ran the DAX to create the ProgramSummary table. I then created the new column (with the LOOKUPVALUE code) in the ProgramSummary table.

So at your side, something different is happening. Let me know what you find.