Index Colum

[AlexisKMX]

Hey everyone!

I'm new at working with DAX functions and i am struggling with this. 

I want to create an index colum for the PASSED tests performed per week. I want that the index count starts over each start of a new week and also that it only takes into account the "PASSED" tests. 

Also, this table shows only data for 2022, but i have other tests performed in past years.

My idea is to create an slicer in Power BI that filters for Week Number and Year Number, and displays the information of the PASSED tests performed in the selected Week Number of the Year Number selected. 

I would appreciate any other new ideas to achieve this. Thanks people!

[samdthompson · Accepted solution]

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"

[AlexisKMX]

Thanks @tackytechtom and @samdthompson ! Both methods works for me! I just found a little bit more complicated the PQ way but I was able to make it work.

Thanks for your time.

[tackytechtom · Accepted solution]

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/

[samdthompson · Accepted solution]

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"