How to display recidivist entires in a query?

[JChris]

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?

[JChris · Accepted 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 🙂

[v-shex-msft]

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

[Greg_Deckler]

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.

[JChris]

Thanks for the reply, could you give an example with the data I provided? I tried using DATEDIFF, but it didn't work as expected.

[Greg_Deckler]

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.

[JChris]

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.