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

     

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

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