How do you create a unique row value combination ID to a table?

[MacJasem]

Hi Guys

You never fail to help out with a powerbi "puzzle" 😄

I've got a dimension table with the following columns:

Dimension_ID	Department	Business area	Function	Type	Unique_ID
1	Adminitration	null	null	null	?
1	Air	null	null	null	?
1	null	Sea	null	null	?
1	null	null	Import	null	?
1	null	null	null	Forwarding	?
1	Air/Sea/Rail	null	null	null	?
1	null	null	Domestic	null	?
2	Sea	null	null	null	?
2	Rail	null	null	null	?
2	Continent	null	null	null	?
2	null	Continent Road	null	null	?
2	null	null	Export	null	?
2	null	null	null	Forwarding	?
2	null	null	Domestic	null	?

As you can see the dimension id's i get are the same for different combination of row values. 

what  i want is to create a unique_id column that gives a unique id to every row combination that is unique.

Next question is, the Dimension ID is the relation to the fact table. I can't remove duplicates in that column then i'll loose a lot of row value combinations that i need, and if i create this Unique_ID column i'd want it to become the relationship tie between the dimension and fact table, but not sure that is possible. 

Any idea how i can untangle this dilemma?

[v-cgao-msft · Accepted solution]

Hi @MacJasem ,

See if these two columns meet the requirements:

Unique_ID 1 = CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE([Dimension_ID], [Department]), [Business area]), [Function]), [Type])

Unique_ID 2 = RANKX(ALL('Table'), CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE([Dimension_ID], [Department]), [Business area]), [Function]), [Type]), , , Dense)

Output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum