Count changes in a value/column

[Locco]

I have a set of data and I need to count the amount of times there were changes in a value.

My data is setup similar to this...

TableA

DateTime	Part	Quantity
9/20/2021 12:00:00 am	8250	720
9/20/2021 1:00:00 am	9142	84
9/20/2021 2:00:00 am	8250	800
9/20/2021 3:00:00 am	8250	95
9/20/2021 4:00:00 am	7748	91
9/20/2021 5:00:00 am	9142	700

For this table, the returned value would be 5. 

I'm also linking this to another table which has more details regarding the parts. This table is setup similar to this:

TableB

Part	TD	ST
8250	10000	7314
9142	10001	7314
7748	10000	9548
4152	10002	9548
2829	10002	4314
5416	66148	7314

I'm linking table A and B through a relationship on "part." I'm pulling in the TD and ST values from Tableb and using them in the same visualization.

I need unique values for how many times parts were changed, how many times TD changed, and how many times ST changed.

Based on the supplied data, the values should be 5 (part changes), 4 (td changes), and 4 (st changes).

I've tried a few formulas I have found online but I can't get anything to work correctly, or to not just return count/distinct count.

[Greg_Deckler]

@Locco So would this be correct for part changes, I assume it needs to account for both changes in parts and quantity?

Parts Changes =
  COUNTROWS(
    DISTINCT(
      SELECTCOLUMNS('TableA',"Part",[Part],"Quantity",[Quantity])
    )
  ) - 1

I'm not grasphing how to get the changes for TD and ST numbers you presented given your sample data, can you explain?

[Locco]

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

[Greg_Deckler]

@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/3395....
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)

[Locco]

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')

[Greg_Deckler]

@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

[Locco]

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?

[Greg_Deckler]

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

[Locco]

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.

[Greg_Deckler]

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

[Locco]

Thanks @Greg_Deckler, I appreciate your help and patience.

In your book, you are using measures. I've been using calculated columns and never used measures. You have a column, "Changed Column GJD," this also returns the same incorrect value which is in my book.

Should I be using measures or calculated columns?

[Greg_Deckler]

@Locco There is nothing incorrect about Changed Column GJD. It is based on Part, not TD and correctly identifies when the Part has changed. You can use a column or measure. A measure is generally advised if you want the calculation to be dynamic such as based on user input/interaction. Measures are also preferrable in the sense that they do not add to the space consumed by the data model to the same extent as columns. I added a column for the TD calculation. See updated PBIX file attached.

[Locco]

Thanks @Greg_Deckler ,

I've been going over this for hours and I can't figure out what's going on. The only difference between my data and yours is that mine is a merged query and I have much more data.

In your book the formulas work just fine, but in mine they don't produce the same results. The columns produce the incorrect values we've discussed already, and when I use the measure nothing ever populates. On a card it stays blank, and you can see the little circle "thinking" spinning icon in the corner.

Does the fact that I'm merging queries matter?

Back to my original statement regarding multiple tables...

If this formula works for part:

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)

How would I get it to work if PART was on another table? This is the problem I have with TD and ST, those are on another table. I merged queries to bring all the data together, but I don't know if that is causing my issues with the incorrect calculated columns.

[Locco]

It finally updated the card with "Changed Measure Total TD" and instead of the 34 it says in your book, it says 52k. It seems like the measure does not see the filter(s) which have been applied and is applying the measurement to the entire column regards of any filtered/sliced value.

Edit: Was able to fix this by changing ALL in the measure formula to VALUES, but the measure still returns the same value as the column formula (which is not the right value).

[Greg_Deckler · Accepted solution]

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)

[Greg_Deckler]

@Locco Any possibility of you sharing your PBIX either via a shared link or privately via email. That's going to be the quickest resolution to this. Otherwise, I need a lot more information about what is going on in your model like how relationships are defined, etc. I can't imagine why merging the queries would cause a problem. When you are editing your formula for TD, are you catching both of the places you replace part?

Changed Column = 
  VAR __DateTime = [DateTime]
  VAR __PreviousDateTime = MAXX(FILTER('Table',[DateTime]<__DateTime),[DateTime])
  VAR __PreviousPart = MAXX(FILTER('Table',[DateTime]=__PreviousDateTime),[Part]) //change [Part] to [TD} here
RETURN
  IF([Part]=__PreviousPart,0,1) // also need to change [Part] to [TD] here