cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors