cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Locco
Helper III
Helper III

Count changes in a value/column

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

DateTimePartQuantity
9/20/2021 12:00:00 am8250720
9/20/2021 1:00:00 am914284
9/20/2021 2:00:00 am8250800
9/20/2021 3:00:00 am825095
9/20/2021 4:00:00 am774891
9/20/2021 5:00:00 am9142700

 

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

PartTDST
8250100007314
9142100017314
7748100009548
4152100029548
2829100024314
5416661487314

 

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.

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

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

15 REPLIES 15
Greg_Deckler
Super User
Super User

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

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

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

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Thanks @Greg_Deckler ,

 

That still is not returning the desired result.

 

I've copied a small sample of my data, utilizing the formula:

UPDATE_DATEPARTTDChanged ColumnCount of Changed ColumnChanged Column
08/16/2021 0:1211164TD27414111
08/16/2021 0:5114054TD27955111
08/16/2021 1:1314470TD28112111
08/16/2021 1:4614490TD28312111
08/16/2021 2:2014093TD28412111
08/16/2021 2:4514075TD28412111
08/16/2021 3:104344TD21038111
08/16/2021 3:552165TD10454111
08/16/2021 4:302008TD10291111
08/16/2021 5:092006TD10449111
08/16/2021 5:2411438TD34994111
08/16/2021 5:4311436TD34994111
08/16/2021 6:0814168TD34112111
08/16/2021 6:2911444TD35234111
08/16/2021 6:5512255TD27456111
08/16/2021 7:1212254TD27456111
08/16/2021 8:2814324TD34142111
08/16/2021 8:5211380TD10294111
08/16/2021 9:502920TD10329111
08/16/2021 10:292918TD10296111
08/16/2021 10:5411299TD10242111
08/16/2021 11:4214290TD31375111
08/16/2021 12:134294TD16872111
08/16/2021 13:074283TD17754111
08/16/2021 13:284293TD16973111
08/16/2021 13:5714251TD36196111
08/16/2021 14:3514054TD27955111
08/16/2021 14:4814056TD28013111
08/16/2021 15:1014327TD35036111
08/16/2021 16:1014326TD35036111
08/16/2021 16:3811649TD34116111
08/16/2021 16:5811436TD34994111
08/16/2021 17:2311438TD34994111
08/16/2021 17:4514168TD34112111
08/16/2021 18:372987TD13073111
08/16/2021 18:532987TD13073111
08/16/2021 19:372165TD10454111
08/16/2021 21:574282TD16692111
08/16/2021 22:4214182TD16893111
08/16/2021 23:244343TD21038111
08/16/2021 23:4514289TD21038010

 

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"

Greg_Deckler_0-1632235174167.png

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

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?

 

InkedGreg_Deckler_0-1632235174167_LI2.jpg

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

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.

 

tdss.png

 

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

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

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?

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

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.

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

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)

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

@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

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.