Hi Sam,
I have a scenario where I need to identify the recurrence of the same incident category on a computer.
Each incident record tracks the computer name, the date, and the category of the incident.
I want to highlight those computers which had in the past 3 days the same category of incident.
My dataset is more extended with many other categories that classify an incident, but for sake of simplicity, I attached a barebone example. I had figured out I could use the churn and returning customer pattern, but after some hours of testing I got nothing valid.
I don’t need an off the shelf solution, rather a direction to what you think might be the best approach
Total Incidents in Last 3 Days.pbix (77.6 KB)
I tried to use your model but there is something I still don’t get.
My need is to identify recurring incidents in the previous 3 days on the same computer with the same category incident.
My attempts simply failed because I still do not get the gist of the filtering approach and of DAX in general.
I slightly changed the formula proposed by Harsh adding an ALLEXCEPT to ignore all filters on the Data table (the incidents), but the Computer and Category. Also, the <= was making the formula counting also the incident of the current date, but changing to < made the trick so I got the total number of incidents in the previous N days (added a param for debugging purposes).
Thanks for all your suggestions.
I feel I got a better grasp now
Any suggestion is more then welcome!
Regards
Roberto
Total Incidents in Last N Days =
VAR Last_Date = LASTDATE( Data[Date] )
RETURN
CALCULATE( [Total Incidents] ,
FILTER( ALL( Dates ) ,
Dates[Date] > Last_Date - [days Value] &&
Dates[Date] < Last_Date // changed from <= to < because I need to count how many incident in previous N days excluded the current day
),
ALLEXCEPT(Data,Data[Computer], Data[Category]) //remove all filters but on Computer and Category
Hi @Melissa,
thanks for this!
There are 2 differences though: it includes incidents for the current day and on the last date doesn’t catch the repeating NIC issue incident on Server 3. Also @Harsh’s and mine fail on the last date
Okay so TOPN returns the last 3 days including ‘today’, so you’re only looking back 2 days
Adjusting for that and the numbering, you’ll get this. Will you give it a go - thanks!
Total Incidents L3D Melissa =
VAR MaxDate = MAX( Dates[Date] )
VAR MyPC = SELECTEDVALUE( Data[Computer] )
VAR MyCat = SELECTEDVALUE( Data[Category] )
VAR vTable =
FILTER(
CALCULATETABLE( Data,
TOPN( 4, FILTER( ALL( Dates[Date] ), Dates[Date] <= MaxDate ), [Date], DESC )),
Data[Category] = MyCat &&
Data[Computer] = MyPC
)
VAR Result = COUNTROWS( vTable )
RETURN
IF( Result =1, BLANK(),
IF( Result >1, Result -1, Result ))
Still something I don’t get on the last line. All three formulas return 2 while it should be 1 (Server3 NIC issue) but also line 01/12/2020 Server 3 NIC issue on your formula return 1 and should be 0 like Harsh’s and mine.
Moreover can you please point me to some of the videos in the training material to let me understand why MAX(Date) ignores filter context? I banged my head so many times I just copy and past w/o understanding.
Hi @Melissa,
I’m using mine for now, but yours and @Harsh will do the same. I might switch in case I find performance issues. I’ve to work on large set of data and I found than TOPN is not always the best solution. Also filtering can increase queries execution time. It would be nice to have some material on how to build model using large/big data sets.
.
.
