Getting MAX value IF 2 conditions are met

[matthewtjy]

Hi, how do I go about to create a column "Max_Value_Of_Latest_Submit_Dt" where it would calculate the MAX Sales corresponding to the Latest_Submit_Dt for the particular Employee?

In excel I would use the formula {=MAX(IF(A2&D2=A:A&D:D,C:C))} to get the value in E2.

Thanks!

	A	B	C	D	E
1	Employee	Submit_Dt	Sales	Latest_Submit_Dt	Max_Sales_Of_Latest_Submit_Dt
2	A	1 Jan	10	3 Jan	4
3	A	3 Jan	3	3 Jan	4
4	A	3 Jan	4	3 Jan	4
5	B	2 Jan	2	4 Jan	5
6	B	3 Jan	1	4 Jan	5
7	B	4 Jan	5	4 Jan	5
8	C	5 Jan	8	6 Jan	4
9	C	5 Jan	6	6 Jan	4
10	C	6 Jan	4	6 Jan	4

[Jihwan_Kim]

Hi, @matthewtjy 

Sorry for the confuse.

For creating column,

please try below.

Max Sales of Last Submit Date Column =

VAR lastsubmitdate =

CALCULATE( MAX ( 'Table'[Latest_Submit_Dt] ), ALLEXCEPT ( 'Table', 'Table'[Employee] ))

RETURN

CALCULATE (

MAX ( 'Table'[Sales] ),

ALLEXCEPT ( 'Table', 'Table'[Employee] ),

'Table'[Submit_Dt] = lastsubmitdate

)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

[Jihwan_Kim]

Hi, @matthewtjy 

Pleaes try the below DAX measure.

Max Sales of Last Submit Date =
VAR lastsubmitdate =
MAX ( 'Table'[Latest_Submit_Dt] )
RETURN
CALCULATE (
MAX ( 'Table'[Sales] ),
ALLEXCEPT ( 'Table', 'Table'[Employee] ),
'Table'[Submit_Dt] = lastsubmitdate
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

[matthewtjy]

Hi i've tried to key in the DAX formula but no values appeared and there was no formula error also.

Did I do something wrong?

I used the formula
Max Sales of Last Submit Date =
VAR lastsubmitdate =
MAX ( 'Table'[Latest_Submit_Dt] )
RETURN
CALCULATE (
MAX ( 'Table'[Sales] ),
ALLEXCEPT ( 'Table', 'Table'[Employee] ),
'Table'[Submit_Dt] = lastsubmitdate
)

[Jihwan_Kim · Accepted solution]

Sorry,

the above was for the calculated measure.

I posted one more reply that is for the calculated column.

[matthewtjy]

Thank you so much! Your solution is very helpful!