- Ciao friends.
In this video, I want to show you
how to correctly handle scenarios
where you have multiple customers with the same name.
Let's start by looking at the problem.
Look at this report.
I have selected only three customers from the slicer
and I have a table which is showing me the customers
along with its sales amount.
And it looks like the sales are correct, right?
Wrong! Indeed, I do not have only three customers selected.
I have six of them. Why?
Because multiple customers have the same name.
We can take a look at it.
I just duplicate the table.
Then I enlarge it a bit,
and finally, I add the customer code to the table.
Now the customer code is unique.
Therefore, now I correctly see six customers.
You see that Barnes Abigail appears twice.
Blue Nicole the same, and the same for Blue Paige.
The thing is, the figures in this report are correct,
where as these numbers are wrong.
And if you do not add the customer code to your table,
you will see all the values aggregated together.
Now this is a problem.
You don't want to show the wrong numbers,
so we need to fix it.
How to do that?
Well, we will create a calculated column
which I already have in my report
that is called Name Unique
that let me slice correctly, customers,
even though they have the same name.
You see that now Barnes Abigail appears twice.
Blue Nicole does the same,
and ... customers will be separated
even though they have the same name.
How do I do it?
Well, to solve the problem, you need some DAX tricks.
I already have the solution here,
but my goal is to show you how to do it step by step.
So let me just remove the solution, and we do it together.
As I said, we need a calculated column.
So the first step is just that of creating a new column.
Let's give it a name, like new name,
and I start by retrieving only the customer name.
The reason is, I want to show this column in my report
in order to be able to debug it step by step.
So, I need to select the report, and then add the new name.
Next step will be changing the definition of this code.
Where is it? Here it is, no, new name, here it is.
Change the expression step by step.
Now, there is a very simple option to solve the problem,
which unfortunately is not perfect.
I can add at the end, just the customer code.
So, my new calculated column will contain the customer name,
and the customer code in parentheses.
That should be customer code, customer code.
You see, this version now works.
I am correctly seeing the numbers.
I see the customers split,
but what I'm doing is, I'm polluting the customer name
with the customer code.
So this solution is far from being perfect.
Another option is to still separate the customer,
but instead of using the customer code,
I assign to each customer, one name.
So I will have Barnes Abigail one, Barnes Abigail two.
To obtain this goal, what I need to do is first,
list all the codes of the customers with the same name,
and then I assign a ranking to each customer.
So I will then use the customer ranking in the string.
This is a bit harder, so I need some variables
in order to start.
Let's get rid of this whole definition,
and let's start with the first variable,
which I call customers with same name,
where I use CALCULATETABLE
because the result will be a table.
I get the VALUES of the customer code
ALL the different codes of the customers
that are identical except are identical by name.
So I get rid of any filter except the name.
Now this table will contain two rows
for Barnes Abigail, two rows for Blue Nicole.
I then need to compute the position
of the current customer in this list,
and to do that, I use a RANKX.
So I do a RANKX over the customers with the same table
based on the customer code.
And then, I just return the customer name,
and a dot, and finally, the position.
Now as you'll see,
I now have Barnes Abigail one, Barnes Abigail two,
and then Blue Nicole one, Blue Nicole two.
I'm still polluting the customer name,
but it's a bit better than earlier.
Why not replacing the number
with a suitable number of dots?
Instead of using a number and then the full string,
I can use repeat, and repeat the dot
for a number of times which is equal to the position.
And now you see that Barnes Abigail has one dot
or two dots, depending on whether it's the first
or the second customer with the same name.
It's a bit better than earlier, still not perfect.
The last touch is removing that dot,
but we cannot remove it,
so we will replace it with a very special kind
of white space that exists in the Unichar set code,
which is zero with white space.
It's a white space that does not occupy space
when it is written on the screen.
Therefore, it's a character exactly as that dot,
but it will not be visible on the screen.
Its code is 8204,
so if I use UNICHAR 8204,
that should be 8204 instead of the dot.
As soon as I hit Enter,
now you see that Barnes Abigail appears twice.
It's impossible to distinguish one by the other,
but still, these are two different strings.
Therefore, when the engine groups by name,
it will split the name the right way.
Now keep in mind,
this character has a very special meaning in some languages.
There are multiple zero with white spaces
in the UNICHAR set code.
You need to make sure that you use a correct one.
Otherwise, you might produce incorrect results.
Nevertheless, if you have the option of doing it,
this technique is extremely useful for two reasons.
First, you have the option of showing the correct number,
and this is good.
Second, but this is the topic for a different video,
you will have the option of sorting the same names
in different ways depending on other characteristics.
If you are interested in the topic, come to SQLBI.com,
where you will find the full article
with an explanation of the code,
and you will be able to download the Power BI file.
Have fun with DAX!