Count Unique Contact Numbers with the latest Change Date!

[Waraldir]

Good morning comunity, 

i am still relatively new to DAX and need some support, with a task given to me. 

I have a Table.

(Contact No_, mail, messanger, phone, mobil, change date, change clock time). 

Background is, that user can give the permissions that we can reach them via the given option (mail, mobil, etc.) for advertising. 

The Problem is, that for every permission change, a new line is created. 

For example:

Contact No_ | mail | messanger | phone | mobil | change date | change clock time

000001         | 0      | 0                | 1          |      0   |  10.10.2022  | 12:05 AM

000001         | 1      | 0                | 0          |      0   |  11.10.2022  | 12:07 AM

My Problem is, i need to count every option with the newst date and for every single Contact No_ .

What could be a good Workarround for that problem? If you need more Information please ask me :). 

Cheers!

[Waraldir]

Thank you, it worked well 🙂

[johnt75 · Accepted solution]

You could create a calculated table like

Latest data =
GENERATE (
    DISTINCT ( 'Table'[Contact no] ),
    SELECTCOLUMNS (
        CALCULATETABLE (
            TOPN ( 1, 'Table', 'Table'[change date], DESC, 'Table'[change time], DESC )
        ),
        "mail", 'Table'[mail],
        "messenger", 'Table'[messenger],
        "phone", 'Table'[phone],
        "mobile", 'Table'[mobile]
    )
)

that would have the latest values for all customers.