Neda has attended:
Power BI Modelling, Visualisation and Publishing course
Excel Advanced course
Excel Intermediate course
Power BI DAX course
Introduction to Microsoft 365 Copilot course
Power BI Modelling, Visualisation and Publishing course
Changing a Measure we have in a data set to a unique value where
I have a customer ID field that needs to be unique, however it appears that some other business unit in the same tree has started using similar customer IDs to our business unit after migrating on the same ERP, therefore this has led to our invoice line Transactions semantic module to break because it can not handle duplicate customer IDs across separate business units. I need to figure out a way to change the customer ID to combine the business unit name as well as the customer ID and replace the customer ID field with that new field. However I am struggling to achieve this as I can not see what other tables are using customer ID.
RE: Changing a Measure we have in a data set to a unique value w
Hi Neda,
Thank you for the forum question.
I do not know your model and you data set but based on the question, you may be able to solve the issue like this.
To resolve the issue of duplicate Customer IDs across business units in Power BI (Power Desktop), you can create a new calculated column that combines the Business Unit and Customer ID into a unique identifier. Then, you can replace the original Customer ID field with this new one in your model and visuals.
Here’s a step-by-step guide to help you:
Step 1: Create a Unique Customer ID Column
In Power BI, go to the table where your Customer ID and Business Unit fields exist, and create a new column using DAX:
This will concatenate the business unit name and the customer ID with an underscore, making it unique across units.
Step 2: Replace the Old Customer ID
Go through your visuals, measures, and relationships where Customer ID is used.
Replace it with UniqueCustomerID.
Step 3: Identify Where Customer ID Is Used
Since you're unsure where else Customer ID is used, here's how to find out:
Option 1: Use the Model View
Open Model View in Power BI.
Look for the Customer ID field and see which tables and relationships it connects to.
Option 2: Use External Tools
If you have Tabular Editor or DAX Studio installed, you can:
Open your Power BI model in Tabular Editor.
Use the search feature to find all references to Customer ID.
Option 3: Use Dependency View
In Power BI Desktop, go to Model View.
Right-click on the Customer ID field and choose "View Dependencies".
This will show you all the tables, measures, and visuals that depend on it
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector