title: In PowerBI, how can we create a calculated column where values are modified to be unique?
body: <p><em>NOTE: I rephrased the problem in understandable terms as its original context is quite complex</em></p>
  <p>I have a customer database table with columns for: <code>[ID]</code>, <code>[Name]</code>, <code>[Purchase]</code>. A customer can have multiple purchases, so the ID is not unique. Rows with identical IDs always have the same Name.</p>
  <p>Using Power BI, I want to visualize the total purchase amount per customer, where I want the Name displayed on the axes/legends. However, in some cases, two different customers have the same name (different ID, same Name). Based on the name, power BI aggregates the data for these customers, which is not what I want.</p>
  <p>My intended solution is to use DAX to create a new calculated column <code>[Unique Name]</code> where customer names are adjusted (minimally) to guarantee uniqueness for each ID. A minimal example of what I have in mind:</p>
  <pre><code>ID     Name  Purchase   Unique Name
  123456 Jan   5          Jan (1)
  123456 Jan   2          Jan (1)
  123457 Jan   4          Jan (2)
  123458 Pete  3          Pete
  </code></pre>
  <p>(I will still be able to trace these unique names, because their IDs are visible in tooltips). The addition to the name has to be minimal, so appending the ID to the name is not an option (it would look quite ugly and IDs can be long). Moreover, I would like that the new value in the 'Unique name' column is <strong>guaranteed</strong> to be unique for each ID. See this edge case where the second instance of Jan (ID 123458) can't become Jan (2) because Jan (2) already uniquely exists in the Name column:</p>
  <pre><code>ID     Name     Purchase   Unique Name
  123456 Jan      5          Jan (1)
  123457 Jan (2)  2          Jan (2)
  123458 Jan      4          Jan (3)
  </code></pre>
  <p>I am lost on how to create a Unique Name column with these properties in DAX. Can somebody help with this? Thank you very much in advance.</p>