Create Measure to find multiple MAX based on three unique identifiers

[maxs3]

Hello,

I am trying to find the MAX(Items) based on unique identifiers of Number, Group, and Divider

Number has multiple Groups (one to many)

Group has multiple Dividers (one to many).

Red = Rows I am trying to identify

Red Bold Underline = the unique identifers to find that row

Number	Detail	Group	Divider	Items	Cost
65871	1	1	1	1	11.5
65871	1	1	1	2	12.3
65871	1	1	1	3	15.4
65871	1	1	1	4	10.5
65871	1	2	1	1	13.53
65871	1	2	1	2	16.94
65871	1	2	1	3	11.55
62210	1	1	1	1	14.5
62210	1	1	1	2	16.8
62210	1	1	1	3	19.2
62210	1	1	1	4	17.6
62210	1	1	1	5	20.3
62210	1	1	1	6	19.8
62210	1	1	1	7	22.1
67032	1	1	1	1	15.4
67032	1	1	1	2	13.2
67032	1	1	1	3	12.1
67032	1	1	2	1	15.4
67032	1	1	2	2	15.6
67032	1	1	2	3	18.4

Result:

MaxItems is the measure for the MAX(Items) within a unique Number, Group and Divider

AssociatedCost is the adjacent value to MAX(Items) in the above column Cost.

Number	Detail	Group	Divider	MaxItems	AssociatedCost
65871	1	1	1	4	10.5
65871	1	2	1	3	11.55
62210	1	1	1	7	22.1
67032	1	1	1	3	12.1
67032	1	1	2	3	18.4

[v-kelly-msft · Accepted solution]

Hi  @maxs3 ,

Create a measure as below:

Measure = 
var _maxitems=CALCULATE(MAX('Table'[Items]),FILTER(ALL('Table'),'Table'[Number]=MAX('Table'[Number])&&'Table'[Group]=MAX('Table'[Group])&&'Table'[Divider]=MAX('Table'[Divider])))
Return
IF(MAX('Table'[Items])=_maxitems,MAX('Table'[Items]),BLANK())

Put the measure in filter pane>choose measure is not blank.

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[mohammedadnant]

Hi @maxs3 

If i correctly understood your question, pls check this video might help.

https://youtu.be/THDmCmSozt8

Thanks & Regards,

Mohammed Adnan

https://www.youtube.com/c/taik18

[maxs3]

@mohammedadnant, this is not quite what I am looking for.

Using the example you linked for reference, there are no duplicates in "Name" where I have duplicates in mutliple columns.