Excel formulas into DAX

[ninuhvey]

Looking to see if I can easily get something into a column that will operate like my current formula here:

{=MAX(IF([Employee Number]=[@[Employee Number]],[Activity Date Value]))}

Overall, looking to find the most current date of activity for a given employee number.

I've tried the following but it keeps returning the max value regardless of any filters in the report getting changed:

Latest Date = CALCULATE(MAXX('Source Table',[Date Value].[Date]),FILTER('Source Table',[Employee Number]=[Employee Number]))

thanks in advance!

[ToddChitt]

Please include some sample data and the formula you have now.

Suppose you have data as follows

Person A  |  4/4/2022

Person A  |  5/5/2022

Person B  |  1/1/2022

Person B  |  2/1/2022

And you create a MEASURE with the following: 

[Latest Date] = MAX ( 'my table'[my date] )

A table visual that doe NOT include the Person column will show:

5/5/2022

(because that is the MAX across all Persons

But if you include Person, then you should get this:

Person A  | 5/5/2022

Person B  |  2/1/2022

This correctly shows the MAX for each Person.

[ToddChitt]

You just simply need an Aggregate of MAX ( ). Power BI takes care of knowing the dimensionality slice when you display it. So if you set up a table with Employee and a measure that is MAX ( something ), then that will be the MAX for each employee.

Real simple.

[ninuhvey]

Typically yes, this is how it should be working. Both the Date Value and Employee Number exist on the same source table, but it still pulls the most current date in the entire column regardless of the row header.

I've updated the FILTER in this case to be a special role indicator that Penny fulfills. As you can see for Penny, it's simply pulling in the Latest Date of 7/31/2022 in the entire Latest Date column rather than what should be the 5/16 date for both rows (table vs. matrix makes no difference in this sense):

[v-zhangti · Accepted solution]

Hi, @ninuhvey 

You can try the following methods.

Sample data:

Measure:

Latest Date =
CALCULATE (
    MAX ( 'Table'[Date] ),
    FILTER (
        ALL ( 'Table' ),
        [Employee Number] = SELECTEDVALUE ( 'Table'[Employee Number] )
    )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.