Fabric is Generally Available. Browse Fabric Presentations. Work
towards your Fabric certification with the Cloud Skills Challenge.
The below table has 2 columns,
My requirement is Target Column.
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.
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.
VAR _Step1 =
MIN ( 'Table'[Index] ),
'Table'[Index] >= EARLIER ( 'Table'[Index] )
&& 'Table'[Event] = "R"
VAR _Step2 =
FILTER ( _Step1, [Event] <> "R" && [Index] <= EARLIER ( [Index] ) ),
VAR _Step3 =
VAR _MAXR =
MAXX ( FILTER ( _Step2, [Column2] = EARLIER ( [Column2] ) ), [Index] )
IF ( [Index] = [Column1] - 1, _MAXR - [Column2] + 1, 0 )
SUMX ( FILTER ( _Step3, [Index] = EARLIER ( 'Table'[Index] ) ), [Column3] )
Result is as below.
Best Regards,Rico Zhou
If 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.
But a little bit confused about DAX. 🙂
Check out the November 2023 Power BI update to learn about new features.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.