Forum Discussion
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 not have the same areas or number of areas. Please could someone help create the DAX for this?
| Score | Area | Included in new table? | |
| Person 1 | 6 | A | Yes |
| Person 1 | 4 | B | Yes |
| Person 1 | 5 | C | Yes |
| Person 1 | 4 | A | No |
| Person 1 | 4 | B | No |
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]))
11 Replies
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]RETURNIF(
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 acceptedHi,
give this a try:
Regards FrankAT
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!
Source table:
ID Series Type Score Person 1 8001 A A 5 Person 1 8002 A A 5 Person 1 8003 B A 5 Person 1 8004 B A 4 Person 2 8005 A A 6 Person 2 8006 A A 5 Required table:
ID Series Type Score Person 1 8002 A A 5 Person 1 8004 B A 4 Person 2 8005 A A 6 Summarising the table would be fine, however I like your idea of keeping the original table with the "Yes" or "No" columns and then being able to create the measures using that column as a filter.
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]))
