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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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

 

1 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. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

View solution in original post

4 REPLIES 4
MattAllington
Community Champion
Community Champion

It's not clear what the problem is. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.
Anonymous
Not applicable

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

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. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.
Anonymous
Not applicable

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.