DAX reference calculated column previous calculated value, different row, within self formula

[jrojasuk]

Hi,

I wonder if there is any way to reference a different (previous) calculated value within the same column in a different row within the general formula of the calculated column. When I try doing this I get the generic "circular reference error", even though I have keys and dates to be able to clearly differenciate the row context. 

So basically I would like to be able to do something like this ( easily doable in Excel and the rest 😞

A2 := if ( A1 = "Green", "Red", "Green" ) 

Note that the formula here is not important. What I would like to do is to reference a different value in a different row context for the same column.

Any ideas? Would a calculated measure help instead ? 

Thanks for your help.

Jose

[ncraft]

This may be of some help: Cthulhu - Microsoft Power BI Community

On a different post, I had a question about calculating cumulative consecutive values and I couldnt develop a solution my my own. User @Greg_Deckler helped me out a lot by providing the link above. It invloves a method where by a temporary table is generated and then the calculated column references that table, which - at least in my case - gets around the circular reference issue. Maybe it will be helpful for you aswell?

Best of Luck. 

[KHorseman]

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.

[jrojasuk]

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 

[KHorseman]

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?

[jrojasuk]

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

[v-haibl-msft]

@jrojasuk

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

[jrojasuk]

Hi Herbert,

Yes, thanks again. I have already been through many of those 'circular references' discussions.

This is exactly why I posted here to see if anyone else knew of any 'undocumented feature' that would let me accomplish this.

In my case strictly speaking, given the unique key and the clearly resulting different row context, by definition, there should not be any circular reference at run time. It is simply rather lazily treated as such by the parser just because it is easier to implement. I doubt I am the only one doing this kind of thing.

The point is that I have a set of existing BI reports currently being done in Excel, and I want to move them to some other BI suite going forward. I have already done that with three other products so far and I was just starting with Power BI in order to evaluate its capabilities when I got stuck here. I know I can find a hack around all these sets of columns that use this type of formulas, but the effort is really not worth it here.

So, I guess I sit this one out for a few months until someone comes up with a structural 'fix'.

Thanks again.

Jose

[webportal]

Hi @jrojasuk , @v-haibl-msft 

I have a similar issue: I need to get the classification of a customer on a previous date, to determine the current classification.

Thus, it should be possible to fetch that using a filtered version of the current column, since it is a completely different row context.

Have you managed to find a solution yet?

Thanks