Forum Discussion

Locco's avatar
Locco
Helper III
4 years ago
Solved

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 DateTime Part Quantity 9/20/2021 12:00:00 am 8...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    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's avatar
Greg_Deckler
Community Champion
4 years ago

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's avatar
Locco
Helper III
4 years ago

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