I am trying to build the below logic in powerbi. Can anyone help on this, I tried and able to get the result only one part. Data is about millions, and need to think about performance as well.
This is the wifi access analysis I am doing. I have a date field , Name and status (Success, Failure and Incomplete). Below the points to put in the logic
If a person has got more than one entry within 4 hours of gap in datetime (eg: John), if status success is there, it should pick only the records with "Success" alone (Eg: There are four records for John, out of 3 (First 3) are in the gap of 4 hours, so it should be in one group, since "Success" is availabe in one record, only this record should be in o/p. Johns, fourth record with status "Incomplete" should be considered as separate group since it is more than 4 hours gap from the previous record and so this record will be in output.
Paul has got two records, both are not 4 hours gap of the datetime, so both will be in a group and only one record should go to outputs, since success is not there, next priority is for the failure, and the record with Failure status will go to output
Create another column in which you can calculate the 4 hour increments. As this is one of the foundations for your data.
I would then use this to get a count or distinct count of each user.
And finally from there you can then create a calculated measure which would then be able to show what you require?
And I would suggest if you are looking for performance as @MarcelBeug suggested to potentially the column in the Query Editor but the rest as Calculated columns, so that you can leverage the performance of the Vertipaq engine.
Did I answer your question? Mark my post as a solution!