Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Excel formulas into DAX

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!
Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, Anonymous 

     

    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.

4 Replies

  • ToddChitt's avatar
    ToddChitt
    Super User
    4 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        v-zhangti
        Community Support
        3 years ago

        Hi, Anonymous 

         

        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.

  • ToddChitt's avatar
    ToddChitt
    Super User
    3 years ago

    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.