Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
maxs3
Frequent Visitor

Create Measure to find multiple MAX based on three unique identifiers

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

NumberDetailGroupDividerItemsCost
65871111111.5
65871111212.3
65871111315.4
65871111410.5
65871121113.53
65871121216.94
65871121311.55
62210111114.5
62210111216.8
62210111319.2
62210111417.6
62210111520.3
62210111619.8
62210111722.1
67032111115.4
67032111213.2
67032111312.1
67032112115.4
67032112215.6
67032112318.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

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

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.

v-kelly-msft_0-1616135536236.png

And you will see:

v-kelly-msft_1-1616135552217.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

View solution in original post

3 REPLIES 3
v-kelly-msft
Community Support
Community Support

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.

v-kelly-msft_0-1616135536236.png

And you will see:

v-kelly-msft_1-1616135552217.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

mohammedadnant
Impactful Individual
Impactful Individual

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

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

Thanks & Regards,
Mohammed Adnan
Learn Power BI: https://www.youtube.com/c/taik18

@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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors