Creating a new table from DAX displaying only highest values in a category

[MrBenn]

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

[MrBenn]

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.

[Anonymous · Accepted solution]

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]))

[MrBenn]

This is perfect - thank you so much for your help

[MrBenn]

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.

[Anonymous]

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 rows

FilteredTable = distinct(filter(YourTable;YourTable[Included]="Yes"))

[MrBenn]

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!

[Anonymous]

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 generate

which looks what you need

[MrBenn]

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 sense

Thanks again

[Anonymous]

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

[FrankAT]

Hi,

give this a try:

Regards FrankAT

[Anonymous]

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]

RETURN

IF(
    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 accepted