cancel
Showing results for
Did you mean:
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).

 Time ColumnX Index Output =2 12/12/2012 01:00 0 1 0 12/12/2012 01:10 0 2 0 12/12/2012 01:20 0 3 0 12/12/2012 01:30 0 4 0 12/12/2012 01:40 0 5 0 12/12/2012 01:50 1 6 1 12/12/2012 02:00:00 1 7 0 12/12/2012 02:10 0 8 0 12/12/2012 02:20 1 9 0 12/12/2012 02:30 0 10 0 12/12/2012 02:40 0 11 0 12/12/2012 02:50 0 12 0 12/12/2012 03:00 0 13 0 12/12/2012 03:10 0 14 0 12/12/2012 03:20 1 15 1

2 ACCEPTED SOLUTIONS

Accepted Solutions
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)```

Proud to be a Datanaut!

Established Member

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

Hi @scmpm

Below is the result I have got

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

Regards,

Affan

7 REPLIES 7
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)```

Proud to be a Datanaut!

Established Member

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

Hi @scmpm

Below is the result I have got

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

Regards,

Affan

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?

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 this helped you, please mark this post as an accepted solution and like to give KUDOS .

Regards,

Affan

Frequent Visitor

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

c

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

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

Is it possible for you to share the pbix file?

Highlighted
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