topic Re: Calculated Column Index that Restarts from 1 Based on Values in Another Column in DAX Commands and Tips

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

TJK — Mon, 04 Nov 2024 20:36:42 GMT

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
)

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

Anonymous — Tue, 05 Nov 2024 03:14:43 GMT

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.

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

Jihwan_Kim — Tue, 05 Nov 2024 04:29:52 GMT

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

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

TJK — Tue, 05 Nov 2024 13:06:40 GMT

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?

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

Jihwan_Kim — Wed, 06 Nov 2024 03:54:21 GMT

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