cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
scmpm
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 IV
Super User IV

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)

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

affan Solution Sage
Solution Sage

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

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)

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

affan Solution Sage
Solution Sage

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

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 Solution Sage
Solution Sage

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

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.

affan Solution Sage
Solution Sage

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

Is it possible for you to share the pbix file?

Highlighted
scmpm
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
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors