Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Creating a new table from DAX displaying only highest values in a category

I need to create a table using DAX from an existing table as below.  The new table should only contain the highest score in each area.  Staff may have tied highest scores in each area and staff do no...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    I had created a small pbix to play around but now I closed pbi as I thought the problem was solved, so I can't test but if the previous formula was

    FilteredTable =
SUMMARIZE(filter(YourTable;YourTable[Included]="Yes");YourTable[Area];YourTable[Score];"Max personid";Max(YourTable[Personid]))

    and you need also to group for person, should be

    FilteredTable =
SUMMARIZE(filter(YourTable;YourTable[Included]="Yes");YourTable[PersonId]YourTable[Area];YourTable[Score];"Max ID";Max(YourTable[ID]))

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

I like to approach these jobs with a step by step method so it's easier to debug and to understand. 

1) create a calculated column that has a "yes" in your value if the row should be included in the final table. To do so

 

Included =
VAR thisArea=YourTable[Area]
RETURN
IF(
    RANKX(filter(YourTable;YourTable[Area]=thisArea);YourTable[Score])=1;
"Yes";
"No")

So this one will Rank all of the items of same area from 1 to N, putting the highest with 1, the second with 2 etc.
Then if RANK 1 is means that it's the highest so it will set YES.

Here's the result:
 
 

 

Now you just have to create a new table


FilteredTable = filter(YourTable;YourTable[Included]="Yes")
 
Please MARK ACCEPT Solution if accepted