cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JChris Regular Visitor
Regular Visitor

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
JChris Regular Visitor
Regular Visitor

Re: How to display recidivist entires in a query?

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 Smiley Happy

6 REPLIES 6
Super User
Super User

Re: How to display recidivist entires in a query?

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 have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

JChris Regular Visitor
Regular Visitor

Re: How to display recidivist entires in a query?

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.
Super User
Super User

Re: How to display recidivist entires in a query?

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.


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

JChris Regular Visitor
Regular Visitor

Re: How to display recidivist entires in a query?

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.

Community Support Team
Community Support Team

Re: How to display recidivist entires in a query?

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
JChris Regular Visitor
Regular Visitor

Re: How to display recidivist entires in a query?

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 Smiley Happy

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 6 members 1,662 guests
Please welcome our newest community members: