Calculated Column Index that Restarts from 1 Based on Values in Another Column

[TJK]

I am trying to create a DAX calculated column formula that sorts the rows in order based on the "CreatedDate" column and then indexes the rows starting from 1. I want to restart the Index every time the "Net Promoter System" column does not equal "Promoter".

The below formula does not restart the index from 1. How can I adjust it?

Index =
VAR CurrentDate = NPS[CreatedDate]
VAR CurrentRowNumber = NPS[Survey Name]
VAR IsPromoter = NPS[Net Promoter System] = "Promoter"

RETURN
IF(
IsPromoter,
COUNTROWS(
FILTER(
NPS,
NPS[CreatedDate] <= CurrentDate &&
NPS[Survey Name] <= CurrentRowNumber &&
NPS[Net Promoter System] = "Promoter"
)
),
0
)

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

IndexCC =
VAR _condition =
    ADDCOLUMNS (
        NPS,
        "@condition", IF ( NPS[NetPromoterSystem] = "Promoter", 0, 1 )
    )
VAR _group =
    ADDCOLUMNS (
        _condition,
        "@group",
            SUMX (
                FILTER ( _condition, NPS[CreatedDate] <= EARLIER ( NPS[CreatedDate] ) ),
                [@condition]
            )
    )
VAR _index =
    ADDCOLUMNS (
        _group,
        "@index",
            SUMX (
                FILTER (
                    _group,
                    NPS[CreatedDate] <= EARLIER ( NPS[CreatedDate] )
                        && [@group] = EARLIER ( [@group] )
                ),
                1
            )
    )
RETURN
    MAXX (
        FILTER (
            _index,
            NPS[CreatedDate] = EARLIER ( NPS[CreatedDate] )
                && NPS[SurveyName] = EARLIER ( NPS[SurveyName] )
                && NPS[NetPromoterSystem] = EARLIER ( NPS[NetPromoterSystem] )
        ),
        [@index]
    )

[TJK]

Hi @Jihwan_Kim 

Thank you for your response. You understood correctly, and your output is perfect. However, I have "Other" values that show up on the same day as "Promoter". Is it possible to incorporate time as well?

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your message, and it is possible.

Please let me know if you have date column and time column separately in the table, or date&time column (combined) in the table.

If you have the combined column in the table, it is pretty much similar to the above solution. Please try to write the same DAX formula and check whether it works.

However, if you have separate column in the table, I tried to create a new sample like below, and please check the below picture and the attached pbix file.

Or, it would be great if you could share your sample pbix file's link, and then I can try to look into it.

Thank you.

IndexCC = 
VAR _condition =
    ADDCOLUMNS (
        NPS,
        "@condition", IF ( NPS[NetPromoterSystem] = "Promoter", 0, 1 )
    )
VAR _group =
    ADDCOLUMNS (
        _condition,
        "@group",
            SUMX (
                FILTER ( _condition, NPS[CreatedDate]+NPS[CreatedTime] <= EARLIER ( NPS[CreatedDate])+EARLIER(NPS[CreatedTime]) ) ,
                [@condition]
            )
    )
VAR _index =
    ADDCOLUMNS (
        _group,
        "@index",
            SUMX (
                FILTER (
                    _group,
                    NPS[CreatedDate]+NPS[CreatedTime] <= EARLIER ( NPS[CreatedDate] )+EARLIER(NPS[CreatedTime])
                        && [@group] = EARLIER ( [@group] )
                ),
                1
            )
    )
RETURN
    MAXX (
        FILTER (
            _index,
            NPS[CreatedDate]+NPS[CreatedTime] = EARLIER ( NPS[CreatedDate] )+EARLIER(NPS[CreatedTime])
                && NPS[SurveyName] = EARLIER ( NPS[SurveyName] )
                && NPS[NetPromoterSystem] = EARLIER ( NPS[NetPromoterSystem] )
        ),
        [@index]
    )

[v-xianjtan-msft]

Hi @TJK 

Please try the following DAX formula:

Index = 
VAR CurrentDate = NPS[CreatedDate]
VAR CurrentRowNumber = NPS[Survey Name]
VAR IsPromoter = NPS[Net Promoter System] = "Promoter"
VAR InitialIndex = RANKX(ALL(NPS), NPS[CreatedDate], , ASC, DENSE)
VAR TempTable = 
    FILTER(
        ADDCOLUMNS(
            ALL(NPS),
            "TempIndex", RANKX(
                FILTER(
                    ALL(NPS),
                    NPS[Net Promoter System] <> "Promoter"
                ),
                NPS[CreatedDate],
                ,
                ASC,
                DENSE
            )
        ),
        NPS[Net Promoter System] <> "Promoter"
    )
VAR NewIndexValue = 
    IF(
        IsPromoter,
        InitialIndex,
        MAXX(
            FILTER(
                TempTable,
                NPS[CreatedDate] = CurrentDate &&
                NPS[Survey Name] = CurrentRowNumber
            ),
            [TempIndex]
        )
    )
RETURN
NewIndexValue

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.