none
How to increment value of a column conditionally with DAX expression

    Question

  • Hello,

    In my Power Pivot employee table I have two columns, [Compliance] and [Defaulting Count]. The value of the [Defaulting Count] is initially set to 0. I need to increment [Defaulting Count] each time data is refreshed. To achieve this I wrote the below express for the [Defaulting Count] column, but I get the error saying "Column 'Defaulting Count' cannot be found or may not be used in this expression"

    =IF([Compliance] = "Defaulter", [Defaulting Count] + 1, [Defaulting Count])

    Any thoughts on how this can be achieved will be greatly appreciated.

    Thanks

    Tuesday, April 16, 2013 4:26 AM

Answers

  • I know it's a few months late, but to anyone else getting this error:

    In my experience that error is fixed by simply designating the table name before the column name, even if your references are all contained within the table you are currently working in.  Thus, in the above example, the fix would be:

    =IF(TableName[Compliance] = "Defaulter", TableName[Defaulting Count] + 1, TableName[Defaulting Count])

    Thursday, August 22, 2013 12:31 PM

All replies

  • I know it's a few months late, but to anyone else getting this error:

    In my experience that error is fixed by simply designating the table name before the column name, even if your references are all contained within the table you are currently working in.  Thus, in the above example, the fix would be:

    =IF(TableName[Compliance] = "Defaulter", TableName[Defaulting Count] + 1, TableName[Defaulting Count])

    Thursday, August 22, 2013 12:31 PM
  •  Adding Table name before column name also did not resolve this issue. I still get the following error:

    The value for column 'DefaultCount' in table 'Employees' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified.


    Nasir

    Tuesday, September 10, 2013 4:14 AM
  •  Adding Table name before column name also did not resolve this issue. I still get the following error:

    The value for column 'DefaultCount' in table 'Employees' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified.


    Nasir


    Nasir, are you Taleb?

    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Monday, September 16, 2013 12:15 AM
  • Ed, Yes I started using Taleb because my live id was screwed up with Microsoft Account feature. It took almost 1 year to restore my account. I am happy to be back with my account reachnasir@live.com


    Nasir

    Monday, September 30, 2013 6:35 AM