topic Re: Count Unique Contact Numbers with the latest Change Date! in DAX Commands and Tips

Count Unique Contact Numbers with the latest Change Date!

Waraldir — Wed, 07 Dec 2022 09:20:40 GMT

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:

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!

Re: Count Unique Contact Numbers with the latest Change Date!

johnt75 — Wed, 07 Dec 2022 10:15:37 GMT

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.

Re: Count Unique Contact Numbers with the latest Change Date!

Waraldir — Wed, 07 Dec 2022 12:16:54 GMT

Thank you, it worked well 🙂