Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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?
Solved! Go to Solution.
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 🙂
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.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
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!