Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

Returning unique rows based on multiple columns, filtered by date

I have a dataset with values such as:

 

IP address

ID

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?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

3 REPLIES 3
Anonymous
Not applicable

Have a look:

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

It's the same problem in disguise.

Best
D
Anonymous
Not applicable

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 🙂

Anonymous
Not applicable

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.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.