topic Help to solve the below logic using power bi in Desktop
https://community.powerbi.com/t5/Desktop/Help-to-solve-the-below-logic-using-power-bi/m-p/125157#M52989
<P>Dear,</P><P> </P><P>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.</P><P> </P><P>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</P><P> </P><UL><LI>If a person has got more than one entry within <STRONG>4</STRONG> 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.</LI><LI>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</LI></UL><P> </P><P> Input Desired O/P</P><P> </P><TABLE><TBODY><TR><TD>DateTime</TD><TD>Name</TD><TD>Status</TD><TD> </TD><TD>DateTime</TD><TD>Name</TD><TD>Status</TD></TR><TR><TD>2/2/2017 18:58</TD><TD>John</TD><TD>Completed</TD><TD> </TD><TD>2/2/2017 18:58</TD><TD>John</TD><TD>Completed</TD></TR><TR><TD>2/2/2017 19:57</TD><TD>John</TD><TD>Failed</TD><TD> </TD><TD>4/2/2017 18:56</TD><TD>John</TD><TD>Incomplete</TD></TR><TR><TD>2/2/2017 20:56</TD><TD>John</TD><TD>Incomplete</TD><TD><TABLE><TBODY><TR><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD></TR></TBODY></TABLE></TD><TD>2/2/2017 18:56</TD><TD>Paul</TD><TD>Failed</TD></TR><TR><TD>4/2/2017 18:56</TD><TD>John</TD><TD>Incomplete</TD><TD>2/2/2017 18:53</TD><TD>Mathew</TD><TD>Completed</TD></TR><TR><TD>2/2/2017 18:56</TD><TD>Paul</TD><TD>Failed</TD><TD>2/2/2017 18:51</TD><TD>Jose</TD><TD>Incomplete</TD></TR><TR><TD>2/2/2017 18:55</TD><TD>Paul</TD><TD>Incomplete</TD><TD>2/2/2017 18:51</TD><TD>Ani</TD><TD>Completed</TD></TR><TR><TD>2/2/2017 18:53</TD><TD>Mathew</TD><TD>Completed</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>2/2/2017 18:52</TD><TD>Mathew</TD><TD>Failed</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>2/2/2017 18:51</TD><TD>Jose</TD><TD>Incomplete</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>2/2/2017 18:51</TD><TD>Ani</TD><TD>Completed</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE><P> </P><P>Br,</P><P>SHams</P>Thu, 09 Feb 2017 18:28:37 GMTshamsuddeenvp2017-02-09T18:28:37ZHelp to solve the below logic using power bi
https://community.powerbi.com/t5/Desktop/Help-to-solve-the-below-logic-using-power-bi/m-p/125157#M52989
<P>Dear,</P><P> </P><P>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.</P><P> </P><P>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</P><P> </P><UL><LI>If a person has got more than one entry within <STRONG>4</STRONG> 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.</LI><LI>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</LI></UL><P> </P><P> Input Desired O/P</P><P> </P><TABLE><TBODY><TR><TD>DateTime</TD><TD>Name</TD><TD>Status</TD><TD> </TD><TD>DateTime</TD><TD>Name</TD><TD>Status</TD></TR><TR><TD>2/2/2017 18:58</TD><TD>John</TD><TD>Completed</TD><TD> </TD><TD>2/2/2017 18:58</TD><TD>John</TD><TD>Completed</TD></TR><TR><TD>2/2/2017 19:57</TD><TD>John</TD><TD>Failed</TD><TD> </TD><TD>4/2/2017 18:56</TD><TD>John</TD><TD>Incomplete</TD></TR><TR><TD>2/2/2017 20:56</TD><TD>John</TD><TD>Incomplete</TD><TD><TABLE><TBODY><TR><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD></TR></TBODY></TABLE></TD><TD>2/2/2017 18:56</TD><TD>Paul</TD><TD>Failed</TD></TR><TR><TD>4/2/2017 18:56</TD><TD>John</TD><TD>Incomplete</TD><TD>2/2/2017 18:53</TD><TD>Mathew</TD><TD>Completed</TD></TR><TR><TD>2/2/2017 18:56</TD><TD>Paul</TD><TD>Failed</TD><TD>2/2/2017 18:51</TD><TD>Jose</TD><TD>Incomplete</TD></TR><TR><TD>2/2/2017 18:55</TD><TD>Paul</TD><TD>Incomplete</TD><TD>2/2/2017 18:51</TD><TD>Ani</TD><TD>Completed</TD></TR><TR><TD>2/2/2017 18:53</TD><TD>Mathew</TD><TD>Completed</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>2/2/2017 18:52</TD><TD>Mathew</TD><TD>Failed</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>2/2/2017 18:51</TD><TD>Jose</TD><TD>Incomplete</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>2/2/2017 18:51</TD><TD>Ani</TD><TD>Completed</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE><P> </P><P>Br,</P><P>SHams</P>Thu, 09 Feb 2017 18:28:37 GMThttps://community.powerbi.com/t5/Desktop/Help-to-solve-the-below-logic-using-power-bi/m-p/125157#M52989shamsuddeenvp2017-02-09T18:28:37ZRe: Help to solve the below logic using power bi
https://community.powerbi.com/t5/Desktop/Help-to-solve-the-below-logic-using-power-bi/m-p/125245#M53026
<P>If I interpret the logic correctly, then you need:</P><P>all records with status "Completed", and</P><P>all records with status "Failed"</P><P> if there is no record for the same person with status "Completed" within +/- 4 hours</P><P>all records with status "Incomplete"</P><P> if there is no record for the same person with status "Completed" or with status "Failed" within +/- 4 hours</P><P> </P><P>I put your data in an Excel file and created the Power Query (M) code below.<BR />To be honest I have my doubts about performance, but you may give it a try on some test data.</P><P> </P><PRE>let
Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Help solve logic.xlsx"), null, true),
Input_Table = Source{[Item="Input",Kind="Table"]}[Data],
Typed = Table.TransformColumnTypes(Input_Table,{{"DateTime", type datetime}, {"Name", type text}, {"Status", type text}}),
#"Added Custom" = Table.AddColumn(Typed, "Keep",
(x) => if x[Status] = "Completed"
then true
else if x[Status] = "Failed"
then 0 = Table.RowCount(Table.SelectRows(Typed, each [Name] = x[Name] and
[Status] = "Completed" and
[DateTime] >= x[DateTime] - #duration(0,4,0,0) and
[DateTime] <= x[DateTime] + #duration(0,4,0,0)))
else 0 = Table.RowCount(Table.SelectRows(Typed, each [Name] = x[Name] and
([Status] = "Completed" or [Status] = "Failed") and
[DateTime] >= x[DateTime] - #duration(0,4,0,0) and
[DateTime] <= x[DateTime] + #duration(0,4,0,0)))
),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Keep] = true)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Keep"})
in
#"Removed Columns"</PRE><P> </P>Thu, 09 Feb 2017 22:26:52 GMThttps://community.powerbi.com/t5/Desktop/Help-to-solve-the-below-logic-using-power-bi/m-p/125245#M53026MarcelBeug2017-02-09T22:26:52ZRe: Help to solve the below logic using power bi
https://community.powerbi.com/t5/Desktop/Help-to-solve-the-below-logic-using-power-bi/m-p/125261#M53037
<P>Hi <LI-USER uid="2385"></LI-USER> what I would do is the following:</P><P> </P><UL><LI>Create another column in which you can calculate the 4 hour increments. As this is one of the foundations for your data.</LI><LI>I would then use this to get a count or distinct count of each user.</LI><LI>And finally from there you can then create a calculated measure which would then be able to show what you require?</LI></UL><P>And I would suggest if you are looking for performance as <LI-USER uid="17460"></LI-USER> 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.</P>Thu, 09 Feb 2017 23:45:06 GMThttps://community.powerbi.com/t5/Desktop/Help-to-solve-the-below-logic-using-power-bi/m-p/125261#M53037GilbertQ2017-02-09T23:45:06ZRe: Help to solve the below logic using power bi
https://community.powerbi.com/t5/Desktop/Help-to-solve-the-below-logic-using-power-bi/m-p/125707#M53238
<P>Thanks a lot <LI-USER uid="17460"></LI-USER></P><P> </P><P>This is the logic what I am exactly looking for. Great.</P><P> </P><P>However, it takes long time since my data is more (in lak).. I will have to try some alternate solution.</P><P> </P><P>Br,</P><P>Shams</P>Fri, 10 Feb 2017 19:33:58 GMThttps://community.powerbi.com/t5/Desktop/Help-to-solve-the-below-logic-using-power-bi/m-p/125707#M53238shamsuddeenvp2017-02-10T19:33:58ZRe: Help to solve the below logic using power bi
https://community.powerbi.com/t5/Desktop/Help-to-solve-the-below-logic-using-power-bi/m-p/133142#M56872
<P>I had done this using DAX queries.</P><P> </P><P>Br,</P><P>Shams</P>Sat, 25 Feb 2017 16:13:27 GMThttps://community.powerbi.com/t5/Desktop/Help-to-solve-the-below-logic-using-power-bi/m-p/133142#M56872shamsuddeenvp2017-02-25T16:13:27ZRe: Help to solve the below logic using power bi
https://community.powerbi.com/t5/Desktop/Help-to-solve-the-below-logic-using-power-bi/m-p/133153#M56877
<P>To address your performace issues, you need to split your date column in to two columns.</P><P> </P><P>The other should carry the Hour or Minute of the day as an integer. This will make a massive difference over the size of your model and make loading and calculations much faster.</P><P> </P><P>Then you can do the rest in DAX or PQ. When the actions are within 4 hours on the same day it's easy. If they split a day, you just need a basic IF statement to handle that.</P><P> </P><P>If you have millions of rows, you have to avoid having columns that are highly unique like your datetime column.</P>Sat, 25 Feb 2017 19:21:41 GMThttps://community.powerbi.com/t5/Desktop/Help-to-solve-the-below-logic-using-power-bi/m-p/133153#M56877Phil_Seamark2017-02-25T19:21:41Z