Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jeremy19
Helper III
Helper III

How to create ID variable by value an other variable?

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 !

9 REPLIES 9
v-chuncz-msft
Community Support
Community Support

@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]
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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?

@Jeremy19,

 

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

@Jeremy19,

 

You may use DAX below.

Column =
RANKX (
    Table1,
    MINX (
        FILTER ( Table1, Table1[Variable A] = EARLIER ( Table1[Variable A] ) ),
        Table1[Index]
    ),
    ,
    ASC,
    DENSE
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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 !

 

@Jeremy19,

 

You may add a calculated table.

Table =
Table1
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ricardocamargos
Continued Contributor
Continued Contributor

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.