by More often than not we face a situation where we have customer data with duplicate names. The differentiating column is something like a customer code. When we try to summarize our data using the customer name, Power BI aggregates the measures based on the customer name. So different customers (but with the same names) are aggregated resulting in an incorrect representation of the data.
When we summarize the data by Customer Name we may be misled into believing that “Gregory Ramirez” has a 10,909 Sales Value but in reality, there are two customers with the same name. When we break our data by Code, we see that there in fact are two Gregory Ramirez with 7,442 and 3,467 Sales Value.
One solution is to concatenate the Code and Name column and use that instead.
The problem with this approach is that the names get overly complicated and difficult to read, also we end up concatenating the names without any duplicate values.
Solution
The solution is to utilize a very powerful character from the Unicode set known as Zero-width space. It is a special character that affects how to print words used in some writing systems, we are not interested in their usage but the fact that they are invisible to the eye.
The idea is that if we add Zero-width space to the second Gregory Ramirez, then it will look the same to the human eye but for Power BI it will have two different names so it will not end up aggregating measures for them.
The first step is to identify the customer with the same name, rank them using the customer code, and then add spaces respectively. We will utilize DAX for this purpose.
Unique Name =
VAR CustomersWithSameName =
CALCULATETABLE (
SUMMARIZE( Data, Data[Name], Data[Code] ),
ALLEXCEPT ( Data, Data[Name] )
)
VAR Ranking =
RANKX ( CustomersWithSameName, Data[Code],, ASC, DENSE )
VAR Blanks =
REPT ( UNICHAR ( 8204 ), Ranking - 1 )
VAR Result = Data[Name] & Blanks
RETURN
ResultLet’s walk through the DAX
- We first create a table with selected columns and store it in a variable “CustomersWithSameName” using SUMMARIZE DAX
- The idea is to create a temporary table consisting only the columns which are required for our calculations and to remove any filters on them.
- In our case, the table only consists of the three columns so we can simply refer to the table directly.
- We then create another variable “Ranking” using RANKX DAX
- The idea is to create assign a rank based on the code order.
- Then we create another variable “Blanks” using REPT DAX
- The idea behind this is to create the Zero-width characters based on the Rank.
- For our calculation we use Ranking – 1
- Finally we create and return the variable “Result” which is simply a concatenation of the Customer Name and Blanks
Let’s bring the new column into a table and see the result
Here, we can see that the names appear to be similar but Power BI is still breaking the measure and displaying it in the correct format.
You can find the Power BI file and the data used in the file on my GitHub repository here.
Oh my goodness! Amazing article dude! Thanks, However I am encountering troubles with your RSS. I don’t understand why I can’t subscribe to it. Is there anybody else having the same RSS issues? Anybody who knows the solution can you kindly respond? Thanx.
You should be a part of a contest for one of the finest blogs on the net. I most certainly will highly recommend this blog!
Thanks a bunch for sharing this with all of us you actually know what you are talking about! Bookmarked. Kindly also visit my site =). We could have a link exchange contract between us!
Spot on with this write-up, I seriously believe this site needs far more attention. I’ll probably be returning to read more, thanks for the advice!
You ought to be a part of a contest for one of the most useful blogs online. I’m going to highly recommend this site!
I?ve been exploring for a bit for any high-quality articles or blog posts on this sort of area . Exploring in Yahoo I at last stumbled upon this web site. Reading this info So i am happy to convey that I’ve a very good uncanny feeling I discovered just what I needed. I most certainly will make sure to do not forget this site and give it a look on a constant basis.
Spot on with this write-up, I absolutely feel this amazing site needs far more attention. I’ll probably be back again to read more, thanks for the information!
I’d like to thank you for the efforts you’ve put in writing this blog. I am hoping to view the same high-grade content by you later on as well. In truth, your creative writing abilities has encouraged me to get my very own blog now 😉
Heya are using WordPress for your blog platform? I’m new to the blog world but I’m trying to get started and create my own. Do you require any coding expertise to make your own blog? Any help would be really appreciated!
There’s certainly a great deal to learn about this subject. I love all the points you have made.
This is a topic which is close to my heart… Many thanks! Exactly where are your contact details though?
I just added this web site to my google reader, excellent stuff. Can’t get enough!
I truly love your blog.. Great colors & theme. Did you build this site yourself? Please reply back as I’m hoping to create my very own website and want to learn where you got this from or exactly what the theme is called. Cheers!
Hi there! This is my 1st comment here so I just wanted to give a quick shout out and tell you I genuinely enjoy reading through your blog posts. Can you suggest any other blogs/websites/forums that deal with the same topics? Thank you!
Good website! I really love how it is simple on my eyes and the data are well written. I am wondering how I could be notified whenever a new post has been made. I’ve subscribed to your RSS which must do the trick! Have a nice day!
Thanks for the sensible critique. Me & my neighbor were just preparing to do some research about this. We got a grab a book from our area library but I think I learned more from this post. I’m very glad to see such excellent info being shared freely out there.
Hi my friend! I want to say that this post is amazing, nice written and include approximately all important infos. I?d like to see more posts like this.
As soon as I observed this website I went on reddit to share some of the love with them.