Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |