cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

How to display recidivist entires in a query?

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper II
Helper II

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 🙂

View solution in original post

6 REPLIES 6
Highlighted
Super User IV
Super User IV

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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted

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.
Highlighted

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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted

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.

Highlighted
Community Support
Community Support

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]
    )

 

7.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Highlighted
Helper II
Helper II

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 🙂

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors