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 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]))
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]))
This is perfect - thank you so much for your help