Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I'm trying to spot people that have reinstalled software that was previusly removed by the IT team. Let's say I run a report extraction at 2017-10-05, 2017-10-10, 2017-10-15 and 2017-10-20 and have the following data:
ExtractionDate,OwnerID,Hostname,SoftwareName,SoftwarePublisher 2017-10-05,U0001,US100,"Super PDF Toolkit","Shady Co." 2017-10-15,U0001,US100,"Super PDF Toolkit","Shady Co."
2017-10-20,U0001,US100,"Super PDF Toolkit","Shady Co."
As you can see, the user had the software installed on 2017-10-05, the IT removed it after this date, so the report from 2017-10-10 didn't return anything regarding this user. The next report, 2017-10-15, brings back the software and it continues there in the 2017-10-20 report. In other words, the user reinstalled the software somewhere between 2017-10-10 and 2017-10-15.
How can I filter and display that in PBI Desktop, so I can see which users reinstalled a given software after we removed it, and which softwares are those?
Solved! Go to Solution.
So, after some tweakering I found the solution, and I didn't even need a measure, I just used the MATRIX visual with:
Rows:
1. Hostname
2. SoftwareName
Columns:
1. ExtractionDate
Values:
1. First of SoftwareName
On the left top, click on "at the lowest level of data". Now I can see and filter whatever I want. It would be better if I could filter for only the ones that had any change, I'm working on that 🙂
So, after some tweakering I found the solution, and I didn't even need a measure, I just used the MATRIX visual with:
Rows:
1. Hostname
2. SoftwareName
Columns:
1. ExtractionDate
Values:
1. First of SoftwareName
On the left top, click on "at the lowest level of data". Now I can see and filter whatever I want. It would be better if I could filter for only the ones that had any change, I'm working on that 🙂
HI @JChris,
You can try to use below formula to find out the last removed/installed date.(my formula will skip the continued date range)
Last Removed = VAR PreviousDate = MAXX ( FILTER ( ALL ( 'sample' ), [OwnerID] = EARLIER ( [OwnerID] ) && [SoftwareName] = EARLIER ( [SoftwareName] ) && [ExtractionDate] < EARLIER ( [ExtractionDate] ) ), [ExtractionDate] ) RETURN IF ( DATEDIFF ( PreviousDate, [ExtractionDate], DAY ) > 1, PreviousDate ) Last Installed = VAR first_Date = MINX ( FILTER ( ALL ( 'sample' ), [OwnerID] = EARLIER ( [OwnerID] ) && [SoftwareName] = EARLIER ( [SoftwareName] ) ), [ExtractionDate] ) RETURN IF ( [Last Removed] <> BLANK () || first_Date = [ExtractionDate], [ExtractionDate] )
Regards,
Xiaoxin Sheng
You could create a measure that simply does a DATEDIFF between the MAX "ExtractionDate" and the MIN "ExtractionDate". Any DATEDIFF of a DAY interval that is greater than zero would be the tell. If you put that measure into a table along with OwnerID and SoftwareName, you would have what you want I believe.
I created this measure:
Recidivist = DATEDIFF(MIN(recidivist[ExtractionDate]),MAX(recidivist[ExtractionDate]),DAY)
I put OwnerID, Hostname, SoftwareName and Recidivist into a table. Recidivist was 15, the difference in days between the Friday October 20th and Thursday October 5th.
So, it didn't work as expected, the string you posted is the one I used. The number is the same for everyone, but I needed it to be per software, per user. The measure was in a table along with OwnerID and SoftwareName, you would have what you want I believe.
User | Count |
---|---|
85 | |
84 | |
68 | |
66 | |
56 |
User | Count |
---|---|
126 | |
102 | |
90 | |
84 | |
66 |