Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to 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!
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!
Hi @maxs3
If i correctly understood your question, pls check this video might help.
Thanks & Regards,
Mohammed Adnan
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.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
72 | |
69 | |
37 | |
29 | |
25 |
User | Count |
---|---|
89 | |
49 | |
44 | |
38 | |
37 |