Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
Can you please share the my request, output power bi
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
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 Quarter
Prabaharan | 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:
hi Dale, I want to know how could i get the row of "success" in Dax? Thanks in advance.
Had to do some few modifications to suit me even more, but this actually worked. Thanks ! 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |