cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
scmpm Frequent Visitor
Frequent Visitor

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

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

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)

I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

View solution in original post

affan Established Member
Established Member

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

7 REPLIES 7
Super User
Super User

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)

I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

View solution in original post

affan Established Member
Established Member

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

scmpm Frequent Visitor
Frequent Visitor

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?

affan Established Member
Established Member

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

scmpm Frequent Visitor
Frequent Visitor

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.

Highlighted
affan Established Member
Established Member

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

Is it possible for you to share the pbix file?

scmpm Frequent Visitor
Frequent Visitor

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 80 members 1,309 guests
Please welcome our newest community members: