Unique id for a binary column

[Anonymous]

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

[MattAllington]

It's not clear what the problem is. 

[Anonymous]

Sorry Matt. I have just updated it. Please let me know if it does not make sense. 

[MattAllington · Accepted solution]

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 column2

the new column will not be consecutive integers, but there will be unique IDs as you wanted. 

[Anonymous]

Thanks a lot Matt. I appreciate it. I will try it now 👍