Forum Discussion

maxs3's avatar
maxs3
Frequent Visitor
5 years ago
Solved

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

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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!

3 Replies