Index Colum

Hello. You will want to do that bit in PowerQuery since it will be less resource hungry. You are needing a rank by group. Broadly the approach is to Add the index, then group and add the index again. Take particular note of the  Added Custom row of code. this is where the magic happens. Do make sure you have the data sorted in whatever order best suits you ranking needs before you apply these steps.

The Code will be something along these lines:

let

.....

#"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 2, 1, Int64.Type),
#"Grouped Rows" = Table.Group(#"Added Index", {"DATE_DONE"}, {{"Table", each _, type table [WEEK=nullable text, DATE_DONE=nullable text, Index=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Table],"Index2",1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Index2"}, {"Custom.Index2"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Table"})
in
#"Removed Columns"

Hi AlexisKMX ,

Agree a 100% with samdthompson, do it in PQ!

If (due to some reasons) you can not do it in PQ, here a solution in DAX, anyway:

Here the DAX code for a calculated column:

INDEX = 
IF (
    TestTable[State] = "PASSED", 
    RANKX ( 
        FILTER ( 
            'TestTable', 
            TestTable[State] = "PASSED" && 'TestTable'[WEEK] = EARLIER ( 'TestTable'[WEEK] )
        ),
        'TestTable'[Date Done],
        , ASC
        , DENSE
    ),
    BLANK()
)

I used the code snippet from here.

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/