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 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?
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.