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

Sorry I don't think I'm being very clear. "Person 1" is the same person and against each person is a set of results each with an ID - your previous  solution is very close to what I need, but I need to be able to disregard ties within a group by using an ID field (let's say it's an ID of the scores) or index column. i.e. identical scores in the same series with the same type and against the same person are calculated 'Y' for the highest ID.

 

 IDSeriesTypeScoreIncluded
Person 18003AA5Y
Person 18002AA5N

 
I hope that makes sense

 

Thanks again

Anonymous's avatar
Anonymous
Not applicable
6 years ago

can you show what is the expected result? It's easier than going iteratively 🙂