topic Re: Returning unique rows based on multiple columns, filtered by date in DAX Commands and Tips

Returning unique rows based on multiple columns, filtered by date

Anonymous — Tue, 03 Mar 2020 15:42:08 GMT

I have a dataset with values such as:

IP address

Date

pertaining to a scan of the IP address. There are mulitple rows for the same IP address, for different scan dates, and different IDs for various findings. I want to be able to produce the following:

1. Table that has unique rows (based on IP address + ID combination), with the date being in the last month i.e. new IDs appearing

2. Table that shows the opposite i.e. where rows may have different dates, but the same IP address + ID combination, showing that the IP/ID pair has existed for at least one month

3. Counts of how many new IP/ID pairs have shown up in the last scan

4. Counts of how many IP/ID pairs are not new

(3&4 easy enough if I have the tables from 1&2)

I've been trying out the SUMMARIZE, DISTINCT, and VALUES functions but with no joy. Does anybody have a solution or some guidance?

Re: Returning unique rows based on multiple columns, filtered by date

Anonymous — Tue, 03 Mar 2020 15:53:43 GMT

Have a look:

https://www.daxpatterns.com/new-and-returning-customers/

It's the same problem in disguise.

Best
D

Re: Returning unique rows based on multiple columns, filtered by date

Anonymous — Wed, 04 Mar 2020 09:26:42 GMT

This looks like exactly what I'm looking for. Just need to work out how to translate in to my problem.

Thanks for thinking laterally on my behalf! Will mark as accepted, even though it may take me a while to resolve 🙂

Re: Returning unique rows based on multiple columns, filtered by date

Anonymous — Thu, 05 Mar 2020 12:55:22 GMT

For anybody else looking for the same help, I also found this page which takes a slightly different approach but I found the explanations really helpful:

https://finance-bi.com/power-bi-new-and-repeat-customers/

EDIT: although I think some of the logic isn't 100%, it still helped me get what I was after e.g. the author states that FIRSTDATE is used to get the first day of the month, but its used to get the earliest date in a range of dates passed to it, as a column.

In summary, the post above had some useful tips to help me understand a solution for my own problem, but be careful how you implement it yourself.