Forum Discussion
Helper IGetting MAX value IF 2 conditions are met
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 |
Sorry,
the above was for the calculated measure.
I posted one more reply that is for the calculated column.
5 Replies
- Jihwan_Kim
Super User
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.
matthewtjyHi 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
Sorry,
the above was for the calculated measure.
I posted one more reply that is for the calculated column.
- 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] ))RETURNCALCULATE (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.