Forum Discussion
Compare column value with previous row
Hi,
Im having a bit of struggle with DAX when trying to compare a value in a column to the previous row. So far, there doesnt seem to be a good answer that works for me. I have been trying to figure it out for a whole day now which is why i have decided to turn here for help.
What I am trying to do is to count switches from True-to-False and False-to-True for each different name. I need this to understand which names are the most flaky.
So the data would look something like this:
time City name success
4/19/2018 hh:mm:ss LA Jill True
4/19/2018 hh:mm:ss CA Miranda True
4/19/2018 hh:mm:ss CA Miranda False
4/19/2018 hh:mm:ss CA Miranda False
4/20/2018 hh:mm:ss LA Jill True
4/20/2018 hh:mm:ss LA Jill False
4/20/2018 hh:mm:ss LA Miranda True
4/20/2018 hh:mm:ss LA Jill False
4/21/2018 hh:mm:ss LA Jill True
4/21/2018 hh:mm:ss CA Miranda False
So i guess there would be two ways - either to create a new columns that marks 0/False or 1/True whether there has been a change, or to just return the overall number for each name. For this example, the return should be something like this:
Name Switches
Jill 2
Miranda 3
Another thing is that both names can be in different cities, so it should be filterable by city, meaning if i take one city out, the switch would also not take into count the rows that have been filtered out. In this example the name Miranda would have -1 switch if i would take out LA.
Thanks in advance
Hi krtalvis,
Try this formula please. You can check out the demo in the attachment.
Measure 2 = SUMX ( SUMMARIZE ( '180720', '180720'[time], '180720'[City], '180720'[name], '180720'[success], "switch", VAR lastTime = CALCULATE ( MAX ( '180720'[time] ), FILTER ( ALLSELECTED ( '180720' ), '180720'[time] < EARLIER ( '180720'[time] ) ) ) VAR lastSuccess = CALCULATE ( MIN ( '180720'[success] ), FILTER ( ALLSELECTED ( '180720' ), '180720'[time] = lastTime ) ) RETURN IF ( [success] = lastSuccess || ISBLANK ( lastSuccess ) = TRUE (), 0, 1 ) ), [switch] )Best Regards,
Dale
5 Replies
- v-jiascu-msftMicrosoft Employee
Hi krtalvis,
Try this formula please. You can check out the demo in the attachment.
Measure 2 = SUMX ( SUMMARIZE ( '180720', '180720'[time], '180720'[City], '180720'[name], '180720'[success], "switch", VAR lastTime = CALCULATE ( MAX ( '180720'[time] ), FILTER ( ALLSELECTED ( '180720' ), '180720'[time] < EARLIER ( '180720'[time] ) ) ) VAR lastSuccess = CALCULATE ( MIN ( '180720'[success] ), FILTER ( ALLSELECTED ( '180720' ), '180720'[time] = lastTime ) ) RETURN IF ( [success] = lastSuccess || ISBLANK ( lastSuccess ) = TRUE (), 0, 1 ) ), [switch] )Best Regards,
Dale
- krtalvisFrequent Visitor
Had to do some few modifications to suit me even more, but this actually worked. Thanks ! :)
- SherryZhaoFrequent Visitor
hi Dale, I want to know how could i get the row of "success" in Dax? Thanks in advance.
- AnonymousNot applicable
Creation required to power bi:
When I try to compare a value in a column to the row before it, I'm finding it a little difficult with DAX. I'm having trouble finding a satisfactory solution at this time. I chose to come here for assistance because I had been attempting to figure it out for more than a week. Thanks in advance!Counting switches from Training for each employee's name, date, and financial quarter is what I'm attempting to accomplish. (Dont consider switches counting in Leave and Absent.) We have multiple training, this is for sample data.
As a result, the data would resemble this:
Note: Source file for Columns A through C; output for Columns D and E is required.
EMP_NameDateTrainingSwitch Count by MonthSwitch Count by QuarterPrabaharan 8-Jan-23 Excel 0 0 Prabaharan 14-Jan-23 SQL 1 1 Prabaharan 18-Jan-23 Power bi 1 1 Prabaharan 18-Jan-23 Leave 0 0 Prabaharan 20-Jan-23 Leave 0 0 Prabaharan 25-Jan-23 Excel 1 1 Prabaharan 8-Feb-23 Excel 0 0 Prabaharan 14-Feb-23 SQL 1 1 Prabaharan 18-Feb-23 Power bi 1 1 Prabaharan 18-Feb-23 Leave 0 0 Prabaharan 20-Feb-23 Absent 0 0 Prabaharan 25-Feb-23 Power bi 0 0 Prabaharan 8-Mar-23 Excel 0 1 Prabaharan 14-Mar-23 Excel 0 0 Prabaharan 18-Mar-23 Excel 0 0 Prabaharan 18-Mar-23 Excel 0 0 Prabaharan 20-Mar-23 Excel 0 0 Prabaharan 25-Mar-23 Excel 0 0
Screenshot:
- AnonymousNot applicable
Can you please share the my request, output power bi
Thanks in Advance!