Forum Discussion

cottrera's avatar
cottrera
Post Prodigy
5 years ago
Solved

DAX - Max time

Hi

 

I have a table containing the following columns. Date, Name and Time. See example table below. You will see that some names have multiple times on the same date.

I need to create a new column on this table that uses DAX to find the MAX Time for each person by date

 

DateTimeNameMax Time - Expected result
06/04/202111:08:37Anthony 
06/04/202115:40:02Anthony 
06/04/202117:19:52Anthony17:19:52
07/04/202117:17:54Anthony17:17:54
08/04/202110:21:58Anthony10:21:58
21/04/202120:30:00Bradley20:30:00
22/04/202118:30:00Bradley 
22/04/202118:35:00Bradley18:35:00
26/04/202120:00:00Bradley 
28/04/202120:00:00Bradley 
28/04/202122:00:00Bradley 
28/04/202123:59:59Bradley23:59:59
29/04/202121:00:00Bradley21:00:00
06/04/202110:09:51Ludo 
06/04/202112:05:19Ludo 
06/04/202113:03:15Ludo 
06/04/202113:46:57Ludo 
06/04/202116:17:16Ludo16:17:16

 

thank you

Richard

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, cottrera 

    Please check the below.

    It is for creating a new column.

     

     

    Max Time CC =
    VAR currentdate = 'Table'[Date]
    VAR currentanme = 'Table'[Name]
    RETURN
    IF (
    CALCULATE (
    MAX ( 'Table'[Time] ),
    FILTER ( 'Table', 'Table'[Date] = currentdate && 'Table'[Name] = currentanme )
    ) = 'Table'[Time],
    'Table'[Time],
    BLANK ()
    )
     

    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, and give a big thumbs up.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Hi, cottrera 

    Please check the below.

    It is for creating a new column.

     

     

    Max Time CC =
    VAR currentdate = 'Table'[Date]
    VAR currentanme = 'Table'[Name]
    RETURN
    IF (
    CALCULATE (
    MAX ( 'Table'[Time] ),
    FILTER ( 'Table', 'Table'[Date] = currentdate && 'Table'[Name] = currentanme )
    ) = 'Table'[Time],
    'Table'[Time],
    BLANK ()
    )
     

    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, and give a big thumbs up.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

    • cottrera's avatar
      cottrera
      Post Prodigy
      5 years ago

      Thank you again for your quick response. The DAX function did not work at first. This was due to the table being a direct query. When I converted it to import the dax worked fine. 😀