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)
Thanks Greg_Deckler,
That did not work, it only returns a distinct count and not how many times parts actually changed. If a part repeats later then that part isn't counted.
It's possible for a part to repeat multiple times in a column, in this case it would still count as a "1" since the part didn't actually change until a new part was loaded. If that part repeats later in the day, then it would count towards the change total.
For the TD and ST counts, I have merged the queries so this may no longer be an issue and if I get something to correctly count the part changes then that should also work for TD and ST changes.
Very similar issue here, but the solution there I could not get to work for me:
https://community.powerbi.com/t5/Desktop/Count-number-of-changes-of-the-value/m-p/487205
Locco OK, try the MTBF approach. 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/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous
In your case:
Changed Column =
VAR __DateTime = [DateTime]
VAR __PreviousDateTime = MAXX(FILTER('Table',[DateTime]<__DateTime),[DateTime])
VAR __PreviousPart = MAXX(FILTER('Table',[DateTime]=__PreviousDateTime),[Part])
RETURN
IF([Part]=__PreviousPart,0,1)Thanks Greg_Deckler ,
That still is not returning the desired result.
I've copied a small sample of my data, utilizing the formula:
UPDATE_DATE PART TD Changed Column Count of Changed Column Changed Column 08/16/2021 0:12 11164 TD27414 1 1 1 08/16/2021 0:51 14054 TD27955 1 1 1 08/16/2021 1:13 14470 TD28112 1 1 1 08/16/2021 1:46 14490 TD28312 1 1 1 08/16/2021 2:20 14093 TD28412 1 1 1 08/16/2021 2:45 14075 TD28412 1 1 1 08/16/2021 3:10 4344 TD21038 1 1 1 08/16/2021 3:55 2165 TD10454 1 1 1 08/16/2021 4:30 2008 TD10291 1 1 1 08/16/2021 5:09 2006 TD10449 1 1 1 08/16/2021 5:24 11438 TD34994 1 1 1 08/16/2021 5:43 11436 TD34994 1 1 1 08/16/2021 6:08 14168 TD34112 1 1 1 08/16/2021 6:29 11444 TD35234 1 1 1 08/16/2021 6:55 12255 TD27456 1 1 1 08/16/2021 7:12 12254 TD27456 1 1 1 08/16/2021 8:28 14324 TD34142 1 1 1 08/16/2021 8:52 11380 TD10294 1 1 1 08/16/2021 9:50 2920 TD10329 1 1 1 08/16/2021 10:29 2918 TD10296 1 1 1 08/16/2021 10:54 11299 TD10242 1 1 1 08/16/2021 11:42 14290 TD31375 1 1 1 08/16/2021 12:13 4294 TD16872 1 1 1 08/16/2021 13:07 4283 TD17754 1 1 1 08/16/2021 13:28 4293 TD16973 1 1 1 08/16/2021 13:57 14251 TD36196 1 1 1 08/16/2021 14:35 14054 TD27955 1 1 1 08/16/2021 14:48 14056 TD28013 1 1 1 08/16/2021 15:10 14327 TD35036 1 1 1 08/16/2021 16:10 14326 TD35036 1 1 1 08/16/2021 16:38 11649 TD34116 1 1 1 08/16/2021 16:58 11436 TD34994 1 1 1 08/16/2021 17:23 11438 TD34994 1 1 1 08/16/2021 17:45 14168 TD34112 1 1 1 08/16/2021 18:37 2987 TD13073 1 1 1 08/16/2021 18:53 2987 TD13073 1 1 1 08/16/2021 19:37 2165 TD10454 1 1 1 08/16/2021 21:57 4282 TD16692 1 1 1 08/16/2021 22:42 14182 TD16893 1 1 1 08/16/2021 23:24 4343 TD21038 1 1 1 08/16/2021 23:45 14289 TD21038 0 1 0 There's a sum row that doesn't show up here. The first instance of "Changed Column" is with "No Summarization", second is with "Count", last is with "Sum" which returns the same value as "No Summarization."
It is not returning the correct # as a total, when it should give a 0 it is giving a 1. In a few instances the TD # repeats and I believe a 0 should populate.
(I've changed the formula parameters to "TD" instead of "Part" for this test')
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/547907Thanks 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?