Forum Discussion

Yadidya's avatar
Yadidya
Regular Visitor
2 years ago
Solved

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

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
 
 
Need Help
Show and Tell
  • amustafa's avatar
    amustafa
    2 years ago

    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
)

2 Replies

  • amustafa's avatar
    amustafa
    Icon for Solution Sage rankSolution Sage
    2 years ago

    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
)