Forum Discussion
Count changes in a value/column
This turned out to be context issue that was solved by using ALLSELECTED versus ALL:
Changed Column = VAR __DateTime = [DateTime] VAR __PreviousDateTime = MAXX(FILTER(ALLSELECTED('Table'),[DateTime]<__DateTime),[DateTime]) VAR __PreviousPart = MAXX(FILTER(ALLSELECTED('Table'),[DateTime]=__PreviousDateTime),[Part]) RETURN IF([Part]=__PreviousPart,0,1)
Locco Worked for me as a calculated column, which is why it was named "Changed Column". If you want it as a measure you will need:
Changed Measure =
VAR __DateTime = MAX('Table23'[UPDATE_DATE])
VAR __PreviousDateTime = MAXX(FILTER(ALL('Table23'),[UPDATE_DATE]<__DateTime),[UPDATE_DATE])
VAR __PreviousPart = MAXX(FILTER(ALL('Table23'),[UPDATE_DATE]=__PreviousDateTime),[PART])
RETURN
IF(MAX([PART])=__PreviousPart,0,1)
As a column as intended because it was named "Changed Column"
Also, the Total is a different issue: This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Thanks again Greg_Deckler ,
I am using a calculated column and not measures.
I still don't believe the formula is returning the desired result. I've edited the picture you posted. In the circled area, should one of these repeating values be a 0? This is being counted as a change when it wasn't an actual change. There are other occurences of this where the TD did not change, but it seems to be counting as a change. It seems like it only worked on 1 line item where it returned a 0 (TD13073).
Am I misunderstanding?
Locco The column is only based on part, what do you want it based on? I first said if the part changes. Is what you want is only if the TD changes? If so then replace the references for Part to TD.
My apologies Greg_Deckler , I thought your screenshot was set on TD and not PART.
The desired result is still not being returned though, whether I use TD or PART as the column reference.
In the attached screenshot, I have created a column referencing TD and a column referencing PART. There are actually 38 part changes, but the value returned is 41. There are actually 33 TD changes, but the value returned is 40.
Regardless of which column is referenced, the returned value is not correct. When a change doesn't occur, the value in the table should be a 0, correct? If that is true, then there are many instances where it is counting a change when there were none.
Examples:
PART - 5:23 timestamp
TD - 2:45 timestamp, 5:43 timestamp, 7:12 timestamp, 10:53 timestamp, 6:53 timestamp
Those are all being counted as a change when it actually wasn't a change.
Locco I don't get those results at all. See Table23 and Page 5 of attached PBIX below sig. Also the 5:23 PM line does change parts numbers from 11436 to 11438. All I can say is that either we have different data or you aren't translating the DAX correctly.