Forum Discussion
DAX reference calculated column previous calculated value, different row, within self formula
I don't think you can write a formula that refers to itself.
ColumnName = TableName[ColumnName] + 1
...for instance is an invalid formula. I think the only way to get what you want would be to make some sort of secondary helper column to refer back to. The specifics of how that would work depend entirely on the specifics of what you want to do.
Yes, thanks, but surely, with all those structural concepts of Row Context and Filter Context, there must be a way of extracting a previously calculated unique row/column value within the same column range. After all a calculated column is supposed to be a "fixed" permanent entity added to the table. That is what makes it different from a measure among other things.
What is the architectural design thought around this? Surely this is not a new odd requirement, but something that happens quite frequently all over the place.
After all Excel itself easily supports this concept, not to mention other competitor's products.
Of course, there is always a hack around it, but that should not be the way. In the worse case scenario I could calculate everythig in either Excel or SQL and just load the final data into the BI for presentation and colouring. But then what is the added value?
Wish list feature?
Jose
Excel doesn't support this, because you're talking about two different things. You can write an Excel formula that refers to another row in the same column, but you can't write a formula that refers to itself. This isn't Excel. A column in Excel is a canvas with locations, and you can put different formulas in different locations. A column in Excel can contain potentially a million unique formulas. And the column itself exists independent of the formulas. If there are no formulas in the cells, the column is still there. A column in a database isn't the same thing. The column is defined by a single formula, and the formula is the same for every row.
You can write a column formula that looks at other rows in the same table. Whatever result you're trying to get is probably possible by a different method. This isn't Excel and it isn't really meant to work like Excel. It has far more in common with a database than it does with a spreadsheet.
What are the actual results you're trying to get?
Hi,
I understand, but before we get off track here, and as I mentioned before, I would like to be able to retrieve a previously calculated value in a different row for the same column I am trying to calculate the value now. So, basically I am not trying to get the new value before it is known, but I would like to retrieve a previously calculated value in different row within the same column range.
Something as simplistic as this:
A2 := if ( A1 = "Green", "Red", "Green" )
Where , A is the column we are calculating, A1 => row 1 , A2 => row 2
In DAX something like this for a calculated column called Peak:
Peak =
VAR MaxRow = CALCULATE(MAX(TABLE1[key]), FILTER(ALL(TABLE1), TABLE1[key] < EARLIER(TABLE1[key])))
VAR PrevPeak = CALCULATE(SUM(TABLE1[Peak]), FILTER(ALL(TABLE1), TABLE1[key] = MaxRow))
RETURN
IF ( TABLE1[CumPnl] > PrevPeak, TABLE1[CumPnl], PrevPeak )
Note that the actual calculation logic is not that important. It is mainly an example. In this case there are other ways of doing this, but I do have more complex calculations of this sort.
Thanks,
Jose
I don’t think we can do it with DAX. We can use EARILER to reference previous values in others existing columns but cannot do the same thing in self calculated column.
In your above DAX formula, Table1[Peak] is referenced in the Peak formula, this will causes a circular dependency. You can take a look at this article which talks about the circular dependencies.
Best Regards,
Herbert
