Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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

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)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

affan
Solution Sage
Solution Sage

Hi @Anonymous

 

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

Hi @Anonymous

 

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

 

 

 

 

 

Greg_Deckler
Super User
Super User

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)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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?

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

Anonymous
Not applicable

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.

Is it possible for you to share the pbix file?

Anonymous
Not applicable

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.