Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 !
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] )
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?
The formula above works for me. Show the expected result more precisely.
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
You may use DAX below.
Column = RANKX ( Table1, MINX ( FILTER ( Table1, Table1[Variable A] = EARLIER ( Table1[Variable A] ) ), Table1[Index] ), , ASC, DENSE )
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 !
You may add a calculated table.
Table = Table1
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |