Forum Discussion

Murali777's avatar
Murali777
Icon for Helper III rankHelper III
4 years ago
Solved

Calculated Table for distinct Email Id as Key and most recent value of Name

I need derived table for distinct email id with the latest first name and last name based on latest registration date time colum (DateTime column). Please find the below example for your reference. ...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    Murali777 , DAX code

    filter(addcolumns(Table, "_Max", maxx(filter(Table, Table[Email] = earlier([Email])), [registration Date])), [_Max] = [registration Date])

     

    or

    new Table  =

    Var _1 = addcolumns(Table, "_Max", maxx(filter(Table, Table[Email] = earlier([Email])), [registration Date]))

    return

    calculatetable(_1, filter(_1, [_Max] = [registration Date]) )

     

Murali777's avatar
Murali777
Icon for Helper III rankHelper III
4 years ago

Hi Amit,

 

I need code for calculated table not in the query editor. Kindly help me for DAX code for calculated Table.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

Murali777 , DAX code

filter(addcolumns(Table, "_Max", maxx(filter(Table, Table[Email] = earlier([Email])), [registration Date])), [_Max] = [registration Date])

 

or

new Table  =

Var _1 = addcolumns(Table, "_Max", maxx(filter(Table, Table[Email] = earlier([Email])), [registration Date]))

return

calculatetable(_1, filter(_1, [_Max] = [registration Date]) )

 

  • Murali777's avatar
    Murali777
    Icon for Helper III rankHelper III
    4 years ago

    Hi Amit,

     

    Thanks for your code and your code is almost working. but my challenge is, i'm having bunch of columns and it returns all the column. 

     

    I want only the 3 columns (Email, First Name, Last Name) in the calculated table.