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]))
The calculated column would be a much better solution, thanks. However I can't get it to work as I forgot to mention there are other columns at play - a time series column 'series' and a 'type'. the 'person' gets scores for all series and types, so that:
Quarter 1
| score | area | series | type | (included) | |
| Person 1 | 5 | A | Quarter 1 | Projection | Y |
| Person 1 | 5 | A | Quarter 1 | Projection | N |
| Person 1 | 6 | A | Quarter 2 | Projection | Y |
| Person 1 | 5 | A | Quarter 2 | Projection | N |
| Person 1 | 5 | A | Quarter 2 | Target | Y |
| Person 1 | 5 | A | Quarter 2 | Target | N |
I also need to exclude tied results, which I don't think the original suggestion does - e.g if person 1 gets a high score of 6, this is only represent in one row, rather than counting as 'Y' for any rows with the same highest score.
I've tried the below but without success. Where am I going wrong?
Included =
VAR thisArea=mytable[area]
VAR thisSeries=mytable[series]
VAR thisType=mytable[type]
RETURN
IF(RANKX(FILTER(mytable,mytable [disc_code]=thisDisc&&mytable[series]=thisSeries&&mytable [type]=thisType),mytable[score])=1,"Y","N")
Any help greatly appreciated.
hi
regarding your formula to use other columns to group, it's correct, maybe clean up the spaces, so it should work.
Included = VAR thisArea = mytable[area] VAR thisSeries = mytable[series] VAR thisType = mytable[type] RETURN IF ( RANKX ( FILTER ( mytable, mytable[disc_code] = thisDisc && mytable[series] = thisSeries && mytable[type] = thisType ), mytable[score] ) = 1, "Y", "N" )Regarding the ties, i'm not sure how you want to handle a case like this:
This person has two IDENTICAL rows, how could you define which one is Y and which one is N?
You can keep my solution and in this way BOTH of these rows will be "Y".
Then your filtered table will have to use DISTINCT, that will remove all duplicated rowsFilteredTable = distinct(filter(YourTable;YourTable[Included]="Yes"))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 =SUMMARIZE(filter(YourTable;YourTable[Included]="Yes");YourTable[Area];YourTable[Score];"Max personid";Max(YourTable[Personid]))
which will generatewhich looks what you need