Forum Discussion
Creating a new table from DAX displaying only highest values in a category
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]))
Brilliant - thank you! I'm almost there with it - with regards to ties, it doesn't matter which is "Y" and "N". I also have a unique ID column to differentiate between them. If I could ask one last thing - how might in corporate the unique ID so that only the tie with the highest ID number is "Y"?
Many thanks for all of your help with this!
So then is a different question, as you put all "Person 1" and they looked like all the same person.
So yuor result (simplified, removing other grouping columns) at the moment is this
but in this example you would like to have only the second, third and fourth rows (so for each Area, highest score and highest person id)? So it's not anymore a DISTINCT as the person id will be different. You need to group and return only the max, so I replicate FrankAT and use
FilteredTable =
which will generate
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.
ID Series Type Score Included Person 1 8003 A A 5 Y Person 1 8002 A A 5 N
I hope that makes senseThanks again
can you show what is the expected result? It's easier than going iteratively 🙂