Index column according to another field

[Syndicate_Admin]

I need to number a table based on data from another field, for example:

INDEX FIELD

A 1

A 2

A 3

B 1

B 2

C 1

C 2

C 3

What can I use to number the index field considering the first field?

[v-henryk-mstf · Accepted solution]

Hi @Syndicate_Admin ,

According to your needs, you can try the following dax formula to group and number the fields through index. The reference is as follow:

Col_gorup = 
VAR CurrentValue = Sheet2[Index]
RETURN
    COUNTROWS (
        FILTER (
            CALCULATETABLE ( Sheet2, ALLEXCEPT ( Sheet2, Sheet2[Column1] ) ),
            Sheet2[Index] <= CurrentValue
        )
    )

If the problem is still not resolved, please provide detailed error information and your expected result. Looking forward to your reply.

Best Regards,
Henry

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

[Syndicate_Admin]

Hello!!!

I need to do exactly the same thing, but I need to transform the data in Power Query. Would I need to replicate that same formula but in M language?

Thank you for your help, greetings!

[mahoneypat]

Please see this video for one way to do this.

Add index to subgroups in Power Query - YouTube

Pat

[Syndicate_Admin]

You can try the LOOKUPVALUE() function, which is similar to excel's VLOOKUP(). I leave the microsoft link where they detail the operation of this function.

The first argument is the column of the table from which you expect the value to be returned, the second is the column by which you will enter with your known value and the third is the known value with which you expect the one you want to return.

https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax