In PowerBI, how can we create a calculated column where values are modified to be unique?

Question

NOTE: I rephrased the problem in understandable terms as its original context is quite complex

I have a customer database table with columns for: [ID], [Name], [Purchase]. A customer can have multiple purchases, so the ID is not unique. Rows with identical IDs always have the same Name.

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.

My intended solution is to use DAX to create a new calculated column [Unique Name] where customer names are adjusted (minimally) to guarantee uniqueness for each ID. A minimal example of what I have in mind:

ID     Name  Purchase   Unique Name
123456 Jan   5          Jan (1)
123456 Jan   2          Jan (1)
123457 Jan   4          Jan (2)
123458 Pete  3          Pete

(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 guaranteed 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:

ID     Name     Purchase   Unique Name
123456 Jan      5          Jan (1)
123457 Jan (2)  2          Jan (2)
123458 Jan      4          Jan (3)

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.

Giovanni Luisotto Giovanni Luisotto · Accepted Answer · 2020-07-30T08:55:47

I would add a "row number" by customer name, for this, you will also need a sorting column that allows the calculated values to don't change in time (otherwise the customer "Joe (2)" might become "Joe (5)" the next time you refresh data), the "Customer ID" will work if it is an incremental number.

The idea is to add the column "Row Number" in your "Customer" table, I would do it directly in PowerQuery. You can then use this column to create your "Unique Name" by concatenating "Name" and "Row Number"

The PowerQuery way (which I suggest), can be found in this post from Radacad: https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

In PowerBI, how can we create a calculated column where values are modified to be unique?

1 Answers