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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
marzuca
New Member

CREATE A COLUMN ID WITH DIFFERENT VALUES FOR EACH PROFILE NAME

On my dataset I have a column colled profile_name, take a look above: 

 

profile_namereviewed_atproduct
Stefane24/03/2015Iphone XR
John24/03/2015Iphone XR
John24/03/2015Iphone XR

 

So as you can see John appears two times, and on my dataset doesn't contains a column to unique identify this person. It's more than three rows. So, how can I create this column with ID but unique for each person? Like the exemple bellow:

 

UNIQUE_IDprofile_namereviewed_atproduct
1442Stefane24/03/2015Iphone XR
253John24/03/2015Iphone XR
253John24/03/2015Iphone XR
3 REPLIES 3
halfglassdarkly
Resolver IV
Resolver IV

I'd say you're in trouble if you're having to generate unique ids from within PowerBI. The problem with using RANKX is that the ranking is going to change as your data expands over time, so it's not really a UID (assuming you're not working with a one off static dataset). If you have a new user called Andrew added to your table in future they would become #1. You might be better off assigning UIDs to profiles in a separate lookup table (e.g. in excel). You also have the issue that profile name is not unique - what happens if you have profiles for two seperate Johns?

marzuca
New Member

@Junaid11 Thank you for the help, but I could not solfe my problem ☹️ I got an error saing that:


MdxScript(Model) (3, 112) Calculation error in measure 'FACT_ALL_REVIEWS'[unique_id]: The 'RANKX' function does not support comparing values of type Text with values of type Number. Consider using the VALUE or FORMAT function to convert one of the values.

 

When trying to do this:

marzuca_0-1679768337326.png

I don't know if it's important, but I created as a calculated measure. Not on the transformation center using the "create personalized column"

 

Junaid11
Helper V
Helper V

Hello @marzuca ,
You can use below code to generate it.

ID = RANKX(ALL('Table'[profile_name]), 'Table'[profile_name],, ASC, Dense)
qfwfqwgqwg.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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