Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Recursive column in DAX

Hey,

How can I achieve the following in a column in a DAX, this sounds a bit like recursive. 

I have  a table like following in PowerBI, in which I wish to populate Column B in the following steps:

1. For first row it should have the value as column A

2. For rows after first row: 

  1. Second row (column B) = 1824 - 0
  2. Third row (column B) = previous row calculated column B value (1824) - current row column A value (24)
  3. and so on
  4. if the column A is blank than populate with previous row calculated column B

 

Row

Column A

Column B

1

1824

1824

2

0

1824

3

24

1800

4

48

1752

5

 

1752

6

 

1752

              

Any help associated with it is highly appreciated.

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous ,

     

    To create two calculated column as below.

     

    Column = 
VAR k =
    FILTER ( 'Table', 'Table'[Column A] <> BLANK () )
VAR minr =
    CALCULATE ( MIN ( 'Table'[Row] ), ALL ( 'Table' ), KEEPFILTERS ( k ) )
RETURN
    IF ( 'Table'[Row] = minr, 'Table'[Column A], 0 - 'Table'[Column A] )

    new column B = 
CALCULATE (
    SUM ( 'Table'[Column] ),
    FILTER ( 'Table', 'Table'[Row] <= EARLIER ( 'Table'[Row] ) )
)

     

     

1 Reply

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi Anonymous ,

     

    To create two calculated column as below.

     

    Column = 
VAR k =
    FILTER ( 'Table', 'Table'[Column A] <> BLANK () )
VAR minr =
    CALCULATE ( MIN ( 'Table'[Row] ), ALL ( 'Table' ), KEEPFILTERS ( k ) )
RETURN
    IF ( 'Table'[Row] = minr, 'Table'[Column A], 0 - 'Table'[Column A] )

    new column B = 
CALCULATE (
    SUM ( 'Table'[Column] ),
    FILTER ( 'Table', 'Table'[Row] <= EARLIER ( 'Table'[Row] ) )
)