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.
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
22.214.171.124 1001 2/1/2018 2/1/2018
126.96.36.199 1002 2/1/2018 2/1/2018
188.8.131.52 1003 2/1/2018 2/1/2018
184.108.40.206 1004 2/1/2018 2/1/2018
220.127.116.11 1005 2/1/2018 2/1/2018
18.104.22.168 1006 2/1/2018 2/1/2018
22.214.171.124 1007 2/1/2018 2/1/2018
126.96.36.199 1008 2/1/2018 2/1/2018
188.8.131.52 1001 2/8/2018 2/8/2018
184.108.40.206 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.
Go to Solution.
OK, taking the example:
VAR next = MINX(FILTER(Repairs,
RETURN IF([RepairType]="PM", 0,IF(ISBLANK(next),
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.
Proud to be a Datanaut!
View solution in original post
See the technique here using EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
@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.
@Greg_Deckler Okay I'm trying this. Thank you for explaing in detail.
No worries, hope you get there. If not, let me know.
@Greg_DecklerIt works fine. Thanks again 🙂
Awesome! Sometimes you can run into trouble with the "X" versions of functions on very large data sets.
Join us in the third Triple A event!
It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.
Make sure you didn't miss any of the things that happened in the community in January!