- Ciao friends.
In this video I want to show you a curious behavior
of DAX, that has to do with case sensitivity.
Now DAX is case insensitive,
meaning that two strings if they differ
only because of the casing, they are considered identical.
This is good whenever you build reports,
because if you store the name of somebody,
no matter whether it's lower case, uppercase
or a mixture of the two,
it's always considered equal and it's merged together.
So it's good when you build reports.
With that said,
if you do not pay attention to this detail,
you might end up doing calculations that produce
strange results, when you mix lower case
and uppercase strings, in the same table.
Because this happens only when you create tables.
And in order to understand
the demos that I'm going to show you later,
I need a bit of introduction,
to understand and to explain you,
how DAX actually stores information.
Now, let me show you this.
Here, I created a table that contains several names
Alberto, Marco, Daniele, and so on.
And these are strings.
But the table that we create with strings,
is not actually stored with strings.
The engine does a different work.
It says, well,
first thing that I need to do is build a dictionary.
To build the dictionary, it scans the table
goes on the first line and says, "Hey I found Alberto."
So, I add Alberto to my dictionary.
And I replace Alberto with 1,
because it's the first string that I find.
Then I go further and I find Marco.
That means I add another row, I name it Marco.
And I replace this with not 1,
but 2, Marco.
Then I go further and I find Daniele
and the process goes on.
So I add the row 3, Daniele,
and I replace this NAME with 3.
And here comes the interesting part
because now I find Daniele again.
And because I found Daniele and it's already here,
I do not need to add anything to the dictionary.
I simply replace Daniele with 3,
Alberto with 2, and Marco with 1.
Now, the structure that DAX actually stores
is the upper one, only numbers
that reference the dictionary.
But the important detail is that,
in order to decide whether a row needs to be added
or not, to the dictionary,
the engine checks for equality.
So it check if this instance of Daniele
was already present here.
And because it found it,
then it did not add it to the dictionary.
This comparison, the check for the presence
of a row in the dictionary is case insensitive.
Therefore, if the dictionary contains
a different version of the same string,
with different cases, it will not create the entry.
And it will replace the string, with the one
the value that was already present in the dictionary.
Now that creates some problems.
Now, let's go to the demo
and see what happens with some DAX code.
I have created this query,
where I have two tables that I define here.
Letters upper that contains an uppercase "A",
and "a", lower case "a", and then a lower case "B".
Whereas letters lower contains a lower case "a",
an uppercase "A" and then a "B", uppercase.
Now, if you evaluate letters upper
as I'm doing right now,
I need to connect the right way.
I'm connected with the wrong model.
Okay?
Now, if I run letters upper,
you see that already something strange happens.
I have "A" uppercase, "a" lower case, and then "b".
The engine created the first entry in the dictionary
which is uppercase.
Then it checked for the second letter
which is a lower case "a",
and instead of inserting lower case "a",
it uses uppercase "A"
because that's the first A that it found in the dictionary.
Whereas for the "b", it uses the lower case.
So, you store a table containing values.
You think that these values are uppercase or lowercase,
depending on what you write,
but that's not what actually the engine stores.
The engine uses the first casing
version of the string.
And then it replaces all the next ones
with the first that it found.
We have seen that with the first table.
Let's do with the second table.
Letters lower.
Letters lower is slightly different
because now we have a lower case "a" first,
then an uppercase "A" and then a "B".
You can try to guess that.
The first will be lower case "a",
the second will be replaced by the first,
and upper case "B" will remain where it is.
And if I run the query, you see the result is lower case "a"
and lower case "a" again, and then uppercase "B".
Now, it's interesting to see what happens
if we manipulate this table.
What if we union them,
we intersect them, or we do other operations?
The algorithm is always the same.
The first occurrence of a string,
is the one that rules and defines,
the casing of all the next ones.
Let's see that.
If I do a union,
of letters lower and letters upper,
because letters lower is the first table,
the first "a" is lower case.
So I would expect this to become lower case,
but also the next ones,
the ones from letter upper will all be lower case.
And if we run it,
you see that the result contains lower case a everywhere,
whereas the B is uppercase,
because the first instance that we found was uppercase.
If you exchange the order, and you use letters upper first
and letters lower later,
what happens is now the A is upper case.
Now this is not a big problem because,
at the end, the strings are absolutely identical.
But it is important, because you might think
that you are processing a table and doing some operations,
but what is stored in the model
is actually something different.
Let me show you that with another example.
Now, what if we define a new table that we compute?
So we have letters upper, we have letters lower.
We define a variable, all lower case,
where I use SELECTCOLUMNS
from letters upper.
And I create a new column lower A,
that uses the LOWER function.
Now LOWER always returns a lower case letter,
and we gain some space.
So all lower case uses LOWER.
Therefore, even though the first "A" is uppercase,
that will become lower case.
And if I EVALUATE all lower case
and I run it, I get an error because,
yes, I messed up everything here,
SELECTCOLUMNS, lower A.
Okay, not yet, comma missing.
You see that lower A has everything which is lower case.
So you might conclude that all lower case works fine.
But as soon as you UNION
your all lower case with letters upper
because in letters upper, the first letter is uppercase
the first "A" is uppercase,
despite my all lower case putting everything in lower case,
the result will contain the A which are still uppercase.
So, what happens is that...
when you just run a query,
or you get the result from a table function
you still have the original result.
But as soon as this result is pushed inside a table,
then the engine will build the dictionary,
will remove duplicates,
and, it will use the casing of the first letter.
Now, this is, as I said, not a big problem
whenever you work with names.
If it's a product name
or if it's a customer name, that is not a big issue.
If that was, a code,
like a product code or an internal code
where casing matters, then this might be a problem.
Because, all the different versions
of the same string will be matched together.
Now, if you are working with Power BI
either the Power BI Service or Power BI Premium,
or Power BI Desktop, you have no options.
DAX is always case insensitive with Power BI.
If you're working with Analysis Services,
there you have the option of
choosing a collation that is case sensitive.
If that is the case, you set up Analysis Services that way.
At that point, uppercase "A" and lower case "a"
they will be considered differently.
They will be two strings separated,
and you will not have this problem.
You will have the problem that
you need to worry about casing
whenever you use names or other kinds of strings.
With that said, I strongly advise
if you have codes in your model, that are
case sensitive, replace them with something different
before importing them in Power BI.
And always work with case insensitive comparison.
Because even though you might be using Analysis Services
and your reports work fine.
If your users extract information from your model
and build their own Power BI data model,
to do further analysis, they will have the problem
and they will have no way and no knowledge
to understand where the problem comes from.
So try to avoid the problem of case sensitivity
first, by being aware of the issue,
and once you understood that,
get rid of it by using always case insensitive comparison.
And if case sensitivity matters for your codes,
handle that by replacing them with integer values
or doing some ETL steps, to clean up your data.
Enjoy DAX.