Filter a dimension based on max value of another field

[yipchunyu]

I created a straight table on the front end want to filter the records by a dimension (based on max value of another value)

=if(FieldA = AGGR(max([FieldA]), [FieldB]), [FieldB])

and then suppress records when is null.

It works for most of the records but some of them isn't working

All the dimension fields will be null for these records (if I clicked suppress when value is null).

Any idea?

[asinha1991]

i did not understand your requirement, can you please elaborate preferably with examples?

[arpitkharkia]

Sample App/data would help a lot in helping out.

[yipchunyu]

Sorry for my poor English.

I have a strange table with 3 dimension fields and some other expression fields

People	Branch Code	WorkOrder	Other expression fields
A	123	10
A	121	11
B	111	12
C	155	13
C	165	14
C	160	15

I want to show the records only with the max(workOrder) by people

People	Branch Code	WorkOrder	Other expression fields
A	121	11
B	111	12
C	160	15

I tried to modify the Branch code field as below

=if([WorkOrder] = AGGR(max([WorkOrder]), [People]), [Branch Code])

and click suppress value when is null. 

Most records are ok but some records are not ok (e.g. missing the people c )

People	Branch Code	WorkOrder
A	121	11
B	111	12

Hope I explained it clear enough this time

[tresesco]

Use FirstSortedValue() like:

Dim : People

Exp1 : FirstSortedValue([Branch Code], - [Work Order])

Exp2 : Max( [Work Order])

[asinha1991]

use something like this and see

Dimension:
1. People
2. BranchCode
3. AGGR(max([WorkOrder]), [People]), [Branch Code])

Expressions

[yipchunyu]

Sorry, I tried your method but still can't make it works.
Actually, there are more than 3 dimension fields. I need to apply the same logic which only get the value based on the max(intStaffSeq)
From:
Staff Code Dept Code Staff Status intStaffSeq Expression Fields
00000C0957 39774 00 - Inforce 379722
00000C0957 39775 61 - Terminated 379723
00000C0957 39776 61 - Terminated 379717

To:
00000C0957 39775 61 - Terminated 379723

[yipchunyu]

Still haven't sort it out yet.  Any advice is welcomed.

[tresesco]

FirstSortedValue() would be the way. Try to create and share a sample app where we can try and help you get the solution right.