cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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.

1 ACCEPTED SOLUTION
Community Support
Community Support

Hi @sharma_mohit ,

 

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] ) )
)

Capture.PNG

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

1 REPLY 1
Community Support
Community Support

Hi @sharma_mohit ,

 

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] ) )
)

Capture.PNG

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors