Forum Discussion
Row Count value assigned to Previous Row
The below table has 2 columns,
| Event | Target |
| C | 0 |
| C | 0 |
| C | 3 |
| R | 0 |
| R | 0 |
| R | 0 |
| C | 0 |
| C | 2 |
| R | 0 |
| R | 0 |
My requirement is Target Column.
Target:
The count of the R value should assign to the previous row where the first R is come appear.
In this example, R will start from the 4th row up to the 6th row. The total Count is 3.
The value 3 should assign to the 3rd row ( previous cell where the first R appears)
Same as the 9th and 10th rows having R, the count is 2. That should assign to the 8th cell.
Thank you.
2 Replies
Hi sivasrao ,
Firstly, I think you need to add an index column in Power Query Editor.
Then you can create a calculated column by below code to achieve your goal.
Target = VAR _Step1 = ADDCOLUMNS ( 'Table', "Column1", CALCULATE ( MIN ( 'Table'[Index] ), FILTER ( 'Table', 'Table'[Index] >= EARLIER ( 'Table'[Index] ) && 'Table'[Event] = "R" ) ) ) VAR _Step2 = ADDCOLUMNS ( _Step1, "Column2", MAXX ( FILTER ( _Step1, [Event] <> "R" && [Index] <= EARLIER ( [Index] ) ), [Column1] ) ) VAR _Step3 = ADDCOLUMNS ( _Step2, "Column3", VAR _MAXR = MAXX ( FILTER ( _Step2, [Column2] = EARLIER ( [Column2] ) ), [Index] ) RETURN IF ( [Index] = [Column1] - 1, _MAXR - [Column2] + 1, 0 ) ) RETURN SUMX ( FILTER ( _Step3, [Index] = EARLIER ( 'Table'[Index] ) ), [Column3] )Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for the reply.
Perfect solution.👍
But a little bit confused about DAX. 🙂
