Forum Discussion
Unique id for a binary column
Hello,
I cannot create the column on the right hand-side (RHS), which is a function of the left hand-side column (LHS). I do not know what function to use.
L | R
----
1 | 1
-----
0 | 1
-----
1 | 2
-----
0 | 2
-----
0 | 2
-----
1 | 3
-----
0 | 3
........
As you can see, the LHS consists of 1's and 0's. Each time there is a 1 in the LHS, there is a unique id in the RHS. This unique id also applies to all 0's below the one. I was trying to use the RANKX function but I was struggling. Can anybody help me with this? I would appreciate any help someone can provide.
Many thanks
You cannot use DAX on this table to solve this problem as presented. Even though you can see the order of the data in the data view, there is no explicit order in the actual loaded data. You could add a unique iD column before loading, but I wouldn't recommend that. Instead, you could solve it in Power Query during load. I assume you data looks like the LH column. there are different ways of solving this, but one easyish way is
add a new index column in power query (let's say it's column2)
Then add a new custom column, like
if [column1] = 0 then null else [column2]
this will then give you a new column 3 containing unique IDs
then you can click on the new column and fill down.
delete column2the new column will not be consecutive integers, but there will be unique IDs as you wanted.
4 Replies
It's not clear what the problem is.
You cannot use DAX on this table to solve this problem as presented. Even though you can see the order of the data in the data view, there is no explicit order in the actual loaded data. You could add a unique iD column before loading, but I wouldn't recommend that. Instead, you could solve it in Power Query during load. I assume you data looks like the LH column. there are different ways of solving this, but one easyish way is
add a new index column in power query (let's say it's column2)
Then add a new custom column, like
if [column1] = 0 then null else [column2]
this will then give you a new column 3 containing unique IDs
then you can click on the new column and fill down.
delete column2the new column will not be consecutive integers, but there will be unique IDs as you wanted.