How to create ID variable by value an other variable?

[Jeremy19]

Hello,

I would create ID variable like this exemple :

Variable A             ID

B                           1

B                           1

B                           1

A                           2

A                           2

C                           3

C                           3

C                           3

And I would like to create a formula for the variable to increment automatically when there is a new value in variable A. Can someone help me?

Thanks !

[v-chuncz-msft]

@Jeremy19,

You may follow the steps below.

1) add an index column in Query Editor

2) add calculated columns in DAX

Flag =
IF (
    LOOKUPVALUE ( Table1[Variable A], Table1[Index], Table1[Index] - 1 )
        = Table1[Variable A],
    0,
    1
)

Column =
SUMX (
    FILTER ( Table1, Table1[Index] <= EARLIER ( Table1[Index] ) ),
    Table1[Flag]
)

[Jeremy19]

I have an error in creating the flag column where it tells me that the syntax for the 1 at the end of the if is incorrect. Sorry but I really don't know the syntax of DAX and so I can't find this error

[Jeremy19]

I found the solution, it works well as I asked, thanks. The only problem is that I can have:

variable A    ID

A                  1

A                  1

B                  2

B                  2

A                 1

In this case this solution doesn't work, and I can't sort my variable A. You have an idea how I could do?

[v-chuncz-msft]

@Jeremy19,

The formula above works for me. Show the expected result more precisely.

[Jeremy19]

Yes it works but in the example of my previous comment the last value for A will not be 1 but 3 with this method

[v-chuncz-msft]

@Jeremy19,

You may use DAX below.

Column =
RANKX (
    Table1,
    MINX (
        FILTER ( Table1, Table1[Variable A] = EARLIER ( Table1[Variable A] ) ),
        Table1[Index]
    ),
    ,
    ASC,
    DENSE
)

[Jeremy19]

Thanks again, you have found the solution again! The problem now is that I can't sort the VarA by Ord because we can't sort by a variable that refers directly or indirectly to it. I didn't think about that ... You know how to solve this problem?

Thanks again !

[v-chuncz-msft]

@Jeremy19,

You may add a calculated table.

Table =
Table1

[ricardocamargos]

Hi @Jeremy19,

You can check this solution:

https://community.powerbi.com/t5/Desktop/Create-conditional-index-column-based-on-row-values-at-Quer...

Ricardo