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