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