How to select a single record for each year based on maximum vaue

[Yadidya]

Hi

Using Summarize, I created a table that has the year, name & point columns. 

Table = SUMMARIZE(constructor_results, races[year], constructors[name], "Total Points", SUM(constructor_results[points]))

It's visualization is attached below. But I want to visualize only one record per year with the name that has the maximum points in that year as a table.  How can I do this? Thanks in advance

[amustafa · Accepted solution]

To achieve this in Power BI using DAX, you can modify your existing SUMMARIZE function to include a ranking based on the points, and then filter the table to only include the top record per year. Here’s how you can do it:

Table = 
VAR SummaryTable = SUMMARIZE(
    constructor_results,
    races[year],
    constructors[name],
    "Total Points", SUM(constructor_results[points])
)
VAR RankedTable = ADDCOLUMNS(
    SummaryTable,
    "Rank", RANKX(FILTER(SummaryTable, [year] = EARLIER([year])), [Total Points], , DESC, Dense)
)
RETURN
FILTER(
    RankedTable,
    [Rank] = 1
)

[Yadidya]

@amustafa Thank you so much for your help. It worked.