Power BI Last Date by Unique Entity

Question

I'm trying to get the last login date by entity.

Table A has a column for Entity ID (non-distinct) and a column for login dates. I've made Table B which is a distinct list of Entity IDs and is related to Table A. I would like to add the last login date as a column (not measure) to Table B.

I tried to use the following function but it just gives me the last date in the table repeated for each row. Somehow I'm not specifying I need the last date by entity even though the relationship is established.

Last Login Date = MAX('Table A'[Login Date])

Any help would be greatly appreciated. Thank you

Answer 1

Last Login Date = MAXX(RELATEDTABLE(TableA), TableA[Login Date])

RELATEDTABLE function selects all relevant records from table A for each record in table B. MAXX then finds max date in the selected table.

Answer 2

to create a grouped table you can try this in power query: Table.Group(Source, {"entity id"}, {{"desired output", each List.Max([login date]), type date}})

Answer 3

Try this as a calculated column in your Table B

Last_Login_Date = 
var tempID = [Entity ID]

return
CALCULATE(
    MAX('Table A'[Login Date]);
    'Table A'[EntityID] = tempID
)