cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
McMur
Helper II
Helper II

Custom column evaluating condition of previous row

I would like to get a custom column like the one presented below (Cond.):

 

Pict22.PNG

 

It needs to evaluate the values of two different columns but just in previous row. In this case conditions are:

 

If Hum. >= 80 and Temp. < 2 (red squares) --> next row in Cond. column = 1 (green squares)

 

I have tried code below, but without success.

 

 

 

IF ( CALCULATE (COUNTROWS ( Table1 ), FILTER ( Table1, EARLIER ( Hum. ) > = 80 && EARLIER ( Temp. ) < 2 ) ), 0 , 1 )

 

 

 

I am not sure about what function to use in order to evaluate the row before. Is that possible?

 

At the same time, I have tried next thread approach to get value from previous row but still does not work.

 

https://community.powerbi.com/t5/Desktop/DAX-getting-previous-value/td-p/489921

 

 

 

 

1 ACCEPTED SOLUTION

Sure,

 

Column = 
    VAR __Previous = MAXX(FILTER('Tabla4',[DateTime]<EARLIER(Tabla4[DateTime])),[DateTime])
    VAR __PreviousTemp = MAXX(FILTER('Tabla4',[DateTime]=__Previous),[Temp.])
RETURN
    IF(__PreviousTemp = 1,1,0)

 

The first line filters the table for all values in the [DateTime] column that are less than the current row's value for the [DateTime] column. Then it uses MAXX to grab the latest date from that filtered set. So, we now have the DateTime that immediately preceeds our current row, assuming that is how things really are in the data set, that things are recorded sequentially.

 

So, we can now use the value for __Previous that we calculated to find the value of the [Temp.] column for that row, __PreviousTemp

 

We then check if the __PreviousTemp is 1 and if so, return 1, otherwise 0.

 


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

@ me in replies or I'll lose your thread!!!

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




View solution in original post

12 REPLIES 12
Greg_Deckler
Super User IV
Super User IV

Yeah @McMur the issue is, "what is next?" I realize that you can look at the table and see the next row down. Unfortunately, DAX does not work like that. There is no guarantee of order. As @amitchandak said, you have to have something that identifies it as "next". Often this is a date/timestamp or an Index column. You have to have some way to filter down to the row you want.

 

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

 


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

@ me in replies or I'll lose your thread!!!

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




Thak you both for your answers.

 

Let's say that I have a way of identify what is "next" based on datetime column (just as @Greg_Deckler said).

 

Captur1.PNG

 

Would it ibe possible in this new scenario identify next row by means of EARLIER function?

 

@McMur , try 1st or second option should work

 

Last count = countx(filter(table,table[datetime]<earlier(table[datetime]) && table[hum] = 80 && table[temp]=1),table[datetime])
OR
Last count = countx(filter(table,table[datetime]<earlier(table[datetime]) && earlier(table[hum]) = 80 && earlier(table[temp])=1),table[datetime])

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Thank you for your answer @amitchandak.

 

I have managed to evaluate your two options (Last_Count_Option1 and Last_Count_Option2). Next pic shows the results:

 

Pict23.PNG

 

As you can see, both options cannot replicate values on column "Cond." which represent the desired outcome.

 

https://www.dropbox.com/s/0zqbl52jhwfanr7/EARLIER_Test.xlsx?dl=0 

 

@McMur - I believe you want this formula:

 

Column = 
    VAR __Previous = MAXX(FILTER('Tabla4',[DateTime]<EARLIER(Tabla4[DateTime])),[DateTime])
    VAR __PreviousTemp = MAXX(FILTER('Tabla4',[DateTime]=__Previous),[Temp.])
RETURN
    IF(__PreviousTemp = 1,1,0)

 

PBIX is attached.

 


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

@ me in replies or I'll lose your thread!!!

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




@Greg_Deckler Thank you for your answer.

 

That formula recreates exactly what I want to get! However, I am trying to understand it but I really do not know how conditions:

Hum. >= 80

Temp. < 2

 are included on it.

In fact, if I update the database with three more rows, It seems no longer works.

 

Pict24.PNG

 

Could you, please, make some comments on this formula?

 

Sure,

 

Column = 
    VAR __Previous = MAXX(FILTER('Tabla4',[DateTime]<EARLIER(Tabla4[DateTime])),[DateTime])
    VAR __PreviousTemp = MAXX(FILTER('Tabla4',[DateTime]=__Previous),[Temp.])
RETURN
    IF(__PreviousTemp = 1,1,0)

 

The first line filters the table for all values in the [DateTime] column that are less than the current row's value for the [DateTime] column. Then it uses MAXX to grab the latest date from that filtered set. So, we now have the DateTime that immediately preceeds our current row, assuming that is how things really are in the data set, that things are recorded sequentially.

 

So, we can now use the value for __Previous that we calculated to find the value of the [Temp.] column for that row, __PreviousTemp

 

We then check if the __PreviousTemp is 1 and if so, return 1, otherwise 0.

 


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

@ me in replies or I'll lose your thread!!!

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




View solution in original post

@Greg_Deckler Thank you very much! Now, I understand how this structure works and I have been able to modify your answer to adapt it to my needs.


At the end, the solution to the question of this thread is next:

 

 

Column = 
    VAR __Previous = MAXX(FILTER('Table2';[DateTime]<EARLIER(Table2[DateTime]));[DateTime])
    VAR __PreviousTemp = MAXX(FILTER('Table2';[DateTime]=__Previous);[Temp.])
    VAR __PreviousHum = MAXX(FILTER('Table2';[DateTime]=__Previous);[Hum.])
RETURN
    IF(__PreviousHum >= 80  && __PreviousTemp <= 2;1;0)

 

 

 

Awesome! Glad to help!

 


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

@ me in replies or I'll lose your thread!!!

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




amitchandak
Super User IV
Super User IV

Is there a way to identify the row, datetime, id etc ??

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@amitchandak The row is the the next at which  Hum. >= 80 and Temp. < 2. That's why I tried to use "EARLIER" function.

 

Earlier do need to create some kind of unique reference to get the previous row.

I think @Greg_Deckler is an expert on this. Greg, please have a look.

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors