Reply
Frequent Visitor
Posts: 7
Registered: ‎11-10-2018
Accepted Solution

Condition if x =1 check last five rows how to do this?

Hi, i need to do this condition on powerbi but dont know how to achieve this.  The condition is get all IF columx=1 AND columx Maxx(from the last five rows) = 0 , 1 , 0). Basically what i am trying to say is when columx X = 1 needs to verify the last rows of columx and see if they are = 0 (if true then output will be 1 if false will be 0). 

 

TimeColumnXIndex Output =2
12/12/2012 01:0001 0
12/12/2012 01:1002 0
12/12/2012 01:2003 0
12/12/2012 01:3004 0
12/12/2012 01:4005 0
12/12/2012 01:5016 1
 12/12/2012 02:00:0017 0
12/12/2012 02:1008 0
12/12/2012 02:2019 0
12/12/2012 02:30010 0
12/12/2012 02:40011 0
12/12/2012 02:50012 0
12/12/2012 03:00013 0
12/12/2012 03:10014 0
12/12/2012 03:20115 1

 


Accepted Solutions
Super User
Posts: 9,834
Registered: ‎07-11-2015

Re: Condition if x =1 check last five rows how to do this?

So this should be something like:

 

Output Column = 
VAR __last5 = SUMX(FILTER('Table',[Index]<EARLIER([Index]) && [Index]>=EARLIER([Index])-5),[ColumnX])
RETURN
IF([ColumnX] = 1 && __last5 = 0, 1, 0)

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

Proud to be a Datanaut!


View solution in original post

Established Member
Posts: 200
Registered: ‎07-12-2017

Re: Condition if x =1 check last five rows how to do this?

Hi @scmpm

 

You can download the PBIX file here

 

Below is the result I have gotIf X=1.png

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

 

Regards,

Affan

 

 

 

 

 

View solution in original post


All Replies
Super User
Posts: 9,834
Registered: ‎07-11-2015

Re: Condition if x =1 check last five rows how to do this?

So this should be something like:

 

Output Column = 
VAR __last5 = SUMX(FILTER('Table',[Index]<EARLIER([Index]) && [Index]>=EARLIER([Index])-5),[ColumnX])
RETURN
IF([ColumnX] = 1 && __last5 = 0, 1, 0)

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

Proud to be a Datanaut!


Established Member
Posts: 200
Registered: ‎07-12-2017

Re: Condition if x =1 check last five rows how to do this?

Hi @scmpm

 

You can download the PBIX file here

 

Below is the result I have gotIf X=1.png

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

 

Regards,

Affan

 

 

 

 

 

Frequent Visitor
Posts: 7
Registered: ‎11-10-2018

Re: Condition if x =1 check last five rows how to do this?

Did that work for you? i am testing mutiple ways but mostly the code is not compiling probably because the if the index is 1 he cant search 5 positons back because dont exist, is there a way to ignore that?

Established Member
Posts: 200
Registered: ‎07-12-2017

Re: Condition if x =1 check last five rows how to do this?

Have you tried the file I have shared? As this has worked for me. I have changed the value of first row for ColumnX to 1 still it is working for me

If X=1 11.png

 

 

 

 

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

 

Regards,

Affan

Frequent Visitor
Posts: 7
Registered: ‎11-10-2018

Re: Condition if x =1 check last five rows how to do this?

Lk5pFrMkRj2O9cz6VvZ6jg.pngc

Cant understand for what reason the code is not compiling, i just add a new column and adpat the code to my case, and just say we are still working to solve the problem (loading ....), i alredy check my datasets to see if there is any value (1) before row 5 and dont exists. i dont know why this error is giving to me. if you knnow why i would be glad.

Established Member
Posts: 200
Registered: ‎07-12-2017

Re: Condition if x =1 check last five rows how to do this?

[ Edited ]

Is it possible for you to share the pbix file?

Highlighted
Frequent Visitor
Posts: 7
Registered: ‎11-10-2018

Re: Condition if x =1 check last five rows how to do this?

Both your aswers work, but when i tryed to compile in every dataset i have at the same time (it take infinite loading) the solution i got for this was to open a new powerbi file and send each data set individually to try see if i would get a error, and for my suprise everything was fine, but cant compile all at the same time weird :/ , probably the problem will be on index when change between datasets who knows.

 

Thanks both for your fast support.

 

Best regards.

scmpm