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

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.

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)

15 Replies

  • 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

       

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        4 years ago

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