cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mudegowdrp Regular Visitor
Regular Visitor

set "first seen" date based on previous records

 

Hi,

 

I have a dataset coming in every week and I have used merged queries to build treands graph to show new records added and see those records which are not resolved.  Please help how can I solve this.

 

Week 1

 

Capture.PNG

 

week 2

Capture.PNG

 

 

 

 

 

I need to add column saying First_Seen

 

So my result dataset should look like

 

IP                                        Vulnerability ID               Date(data exported date)                  First Seen Date

11.1.111.11                          1001                               2/1/2018                                            2/1/2018

11.1.111.12                          1002                               2/1/2018                                            2/1/2018

11.1.111.13                          1003                               2/1/2018                                            2/1/2018

11.1.111.14                          1004                               2/1/2018                                            2/1/2018

11.1.111.15                          1005                               2/1/2018                                            2/1/2018

11.1.111.16                          1006                               2/1/2018                                            2/1/2018

11.1.111.17                          1007                               2/1/2018                                            2/1/2018

11.1.111.18                          1008                               2/1/2018                                            2/1/2018

11.1.111.11                          1001                               2/8/2018                                            2/8/2018

11.1.111.11                          1004                               2/8/2018                                            2/8/2018

 

Same IP will have different Vulnerability ID and vice versa.

But if both are same and occured previously, set first seen date to first occured.

 

Please help.

 

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: set "first seen" date based on previous records

OK, taking the example:

 

Uptime = 
VAR next = MINX(FILTER(Repairs,
                       Repairs[MachineName]=EARLIER(Repairs[MachineName]) && 
                       Repairs[RepairStarted]>EARLIER(Repairs[RepairStarted]) && 
                       Repairs[RepairType]<>"PM"
               ),Repairs[RepairStarted])
RETURN IF([RepairType]="PM", 0,IF(ISBLANK(next),
               DATEDIFF([RepairCompleted],NOW(),SECOND),
               DATEDIFF([RepairCompleted],next,SECOND)
           )
       )

Your first seen would probably look like this:

 

First Seen = 
VAR next = MINX(FILTER(Table,
                       Table[Vulnerability ID]=EARLIER( Table[Vulnerability ID])),
Table[Date(data exported date)]) RETURN IF(ISBLANK(next), [Date(data exported date)],next)

So, what you are doing is creating a temp table inside of MINX that has all of your rows that have the same Vulnerability ID. Then you take the MIN value of the Data column. You return that value.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

7 REPLIES 7
Super User IV
Super User IV

Re: set "first seen" date based on previous records

See the technique here using EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

mudegowdrp Regular Visitor
Regular Visitor

Re: set "first seen" date based on previous records

@Greg_Deckler Thank you. But I did not get that.

 

I should create a column based on IP and Vulnerability ID. If dataset is having same record I should use the FIRST exported as first seen date. if not I should just use exported date.

 

Please help

Super User IV
Super User IV

Re: set "first seen" date based on previous records

OK, taking the example:

 

Uptime = 
VAR next = MINX(FILTER(Repairs,
                       Repairs[MachineName]=EARLIER(Repairs[MachineName]) && 
                       Repairs[RepairStarted]>EARLIER(Repairs[RepairStarted]) && 
                       Repairs[RepairType]<>"PM"
               ),Repairs[RepairStarted])
RETURN IF([RepairType]="PM", 0,IF(ISBLANK(next),
               DATEDIFF([RepairCompleted],NOW(),SECOND),
               DATEDIFF([RepairCompleted],next,SECOND)
           )
       )

Your first seen would probably look like this:

 

First Seen = 
VAR next = MINX(FILTER(Table,
                       Table[Vulnerability ID]=EARLIER( Table[Vulnerability ID])),
Table[Date(data exported date)]) RETURN IF(ISBLANK(next), [Date(data exported date)],next)

So, what you are doing is creating a temp table inside of MINX that has all of your rows that have the same Vulnerability ID. Then you take the MIN value of the Data column. You return that value.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

mudegowdrp Regular Visitor
Regular Visitor

Re: set "first seen" date based on previous records

@Greg_Deckler Okay I'm trying this. Thank you for explaing in detail.

Super User IV
Super User IV

Re: set "first seen" date based on previous records

No worries, hope you get there. If not, let me know.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Highlighted
mudegowdrp Regular Visitor
Regular Visitor

Re: set "first seen" date based on previous records

@Greg_DecklerIt works fine. Thanks again 🙂

Super User IV
Super User IV

Re: set "first seen" date based on previous records

Awesome! Sometimes you can run into trouble with the "X" versions of functions on very large data sets.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors