I would like to get a custom column like the one presented below (Cond.):
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
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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: The Definitive Guide to Power Query (M)
DAX is easy, CALCULATE makes DAX hard...
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
