PowerBI DAX: How to create a calculated column, which increments in value when a certain condition is met

Question

I am trying to create a calculated column [NewCol] in DAX. Everytime The previous value of [Col1] is different than that of the current row, I would want the value of NewCol to increase by 1. How can I achieve this without causing a circulair dependency?

Index	Col1	NewCol
0	200	0
1	200	0
2	201	1
3	201	1
4	201	1
5	202	2
6	203	3
7	202	4
8	202	4

I know this will create a new column [Prv_Col1] which will fetch the pervious value of Col1.

Prv_Col1 = 
    VAR current_row = 'Table1'[Col1]
    VAR previous_row = CALCULATE(MAX('Table1'[Col1]), FILTER('Table1', 'Table1'[Index] = EARLIER('Table1'[Index]) - 1))
    RETURN
        previous_row

And this is the statement if would want to use to determine if the value of NewCol needs to increase by one.

IF('Table1'[Col1]='Table1'[Prv_Col1], False, True)

I would want to avoid creating the column [Prv_Col1], and have no clue on how to increment on itself when creating [NewCol], as reffering to itself causes circular dependancy as the column does not exist yet.

Answer 1

Try the following (added comments to help explain how it works):

NewCol = 
  // an alternative to using EARLIER as it should be faster
  var thisIndex = [Index]

  // get all previous rows including this one
  var upToThisRow =
    FILTER(
      ALL('Table1'),
      'Table1'[Index] <= thisIndex 
    )

  // add a column to this virtual table with a 1 when value changes
  var addCheck = 
    ADDCOLUMNS(
      upToThisRow,
      "check", 
        var thisIndex = [Index]
        var previousValue = 
          CALCULATE(
            MIN('Table1'[Col1]),
              upToThisRow, 
              'Table1'[Index] = thisIndex - 1
            )
        return IF(NOT ISBLANK(previousValue ) && previousValue <> [Col1], 1)
    )
    
  // sum up
  return COALESCE(SUMX(addCheck, [check]), 0)

---

In case it is needed, here's the same with an intermediary Calculate Column (I'm debating with myself if this would perform better than the one above):

ValueChanged = // (intermediary)
  var thisIndex = [Index]
  var prevRowValue = 
    CALCULATE(
      MIN('Table1'[Col1]),
        REMOVEFILTERS(),
        'Table1'[Index] = thisIndex  - 1
    )
  return IF(NOT ISBLANK(prevRowValue) && prevRowValue <> [Col1], 1)


NewCol = 
  var thisIndex = [Index]
  var result =
    CALCULATE(
      SUM('Table1'[ValueChanged]),
      REMOVEFILTERS(),
      'Table1'[Index] <= thisIndex 
    )
  return COALESCE(result, 0)

Additional versions of NewCol to see if they perform better.

NewCol v1 = // added ValueChanged to the filter
  var thisIndex = [Index]
  var result =
    CALCULATE(
      SUM('Table1'[ValueChanged]),
      REMOVEFILTERS(),
      'Table1'[Index] <= thisIndex && [ValueChanged] = 1
    )
  return COALESCE(result, 0)


NewCol v2 =  // using COUNT instead of SUM
  var thisIndex = [Index]
  var result =
    CALCULATE(
      COUNT('Table1'[ValueChanged]),
      REMOVEFILTERS(),
      'Table1'[Index] <= thisIndex && [ValueChanged] = 1
    )
  return COALESCE(result, 0)


NewCol v3 =  // using COUNTROWS instead of SUM
  var thisIndex = [Index]
  var result =
    CALCULATE(
      COUNTROWS('Table1'),
      REMOVEFILTERS(),
      'Table1'[Index] <= thisIndex && [ValueChanged] = 1
    )
  return COALESCE(result, 0)