Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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?
Solved! Go to 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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |