Solved: Row Number help in calculated column

Anonymous · ‎05-22-2025

Hi,

Wanted to have calcualted column based on the Value column >0 based on Date and Partial attributes and it should repeate every month for different partial as mentioned below.

any help

Dt	Partial	Value	Row starts
01-01-2025	L1	0	0
01-01-2025	L1	0	0
01-01-2025	L1	1	1
01-02-2025	L1	1	2
01-02-2025	L1	0	3
01-02-2025	L1	0	4
01-03-2025	L1	0	5
01-03-2025	L1	30	6
01-03-2025	L1	0	7
01-04-2025	L1	25	8
01-04-2025	L1	0	9
01-04-2025	L1	0	10
01-01-2025	L2	0	0
01-01-2025	L2	0	0
01-01-2025	L2	0	0
01-02-2025	L2	0	0
01-02-2025	L2	0	0
01-02-2025	L2	0	0
01-03-2025	L2	3	1
01-03-2025	L2	6	2
01-03-2025	L2	19	3
01-04-2025	L2	0	4
01-04-2025	L2	0	5
01-04-2025	L2	20	6

DataNinja777 · ‎05-22-2025

Hi @Anonymous ,

To calculate a running row number in a calculated column based on Value > 0, grouped by Partial and maintaining the row sequence across time, you can use DAX like this:

Row starts =
VAR CurrentDate = [Dt]
VAR CurrentPartial = [Partial]
VAR CurrentIndex = [Index]
RETURN
IF (
    [Value] > 0,
    CALCULATE (
        COUNTROWS (
            FILTER (
                YourTable,
                [Partial] = CurrentPartial &&
                [Dt] <= CurrentDate &&
                [Value] > 0 &&
                [Index] <= CurrentIndex
            )
        )
    ),
    0
)

This assumes you already have a unique [Index] column that ensures each row has a deterministic order. The logic checks if the current row has Value > 0. If so, it counts all prior rows with the same Partial, date up to the current one, and with Value > 0. This count becomes the row number. If Value is zero, it returns 0. The row number continues to increment for each non-zero value across time, separately for each Partial.

Best regards,

View solution in original post

v-sdhruv · ‎05-30-2025

Hi @Thimma_pbi ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

v-sdhruv · ‎05-27-2025

Hi @Thimma_pbi ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

v-sdhruv · ‎05-25-2025

Hi @Anonymous ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

DataNinja777 · ‎05-22-2025

Hi @Anonymous ,

To calculate a running row number in a calculated column based on Value > 0, grouped by Partial and maintaining the row sequence across time, you can use DAX like this:

Row starts =
VAR CurrentDate = [Dt]
VAR CurrentPartial = [Partial]
VAR CurrentIndex = [Index]
RETURN
IF (
    [Value] > 0,
    CALCULATE (
        COUNTROWS (
            FILTER (
                YourTable,
                [Partial] = CurrentPartial &&
                [Dt] <= CurrentDate &&
                [Value] > 0 &&
                [Index] <= CurrentIndex
            )
        )
    ),
    0
)

This assumes you already have a unique [Index] column that ensures each row has a deterministic order. The logic checks if the current row has Value > 0. If so, it counts all prior rows with the same Partial, date up to the current one, and with Value > 0. This count becomes the row number. If Value is zero, it returns 0. The row number continues to increment for each non-zero value across time, separately for each Partial.

Best regards,