- Ciao friends.
In this video, I want to show you how to sort
one column containing duplicated values, by another
column which again contains duplicated values.
This doesn't look like a very common scenario,
but it's much more frequent than you can imagine.
And it's a powerful technique to learn.
Let's see that with an example.
I have loaded in this model
the census of the United States
and I'm slicing it by state and by county.
What I would like to see are the counties
sorted by number of inhabitants, by their population.
I can do that with Power BI, I can just click
on population and this will sort the entire matrix
by population, but I have two issues that I want to solve.
The first one, not only does the county has been sorted
but also the states, they are no longer
in alphabetical sorting,
and the second, and probably the worst problem,
is that I need to have the population
in the matrix in order to be able to sort.
I cannot sort my matrix by a column that
does not belong to the matrix itself.
So what I would like to do is still use
this alphabetical sorting but have the counties
sorted by population.
I can use the sort by column feature
but unfortunately this is one of the scenarios
where I cannot.
Let's browse the data for a bit,
you see that I have state, name, and population.
I created sum county here, I have
1,077 different counties
but only 800 states, so 800 names,
and 1,075 different values
for the population so both name and population,
they have duplicates, and uh, this is my problem
I cannot sort the name by population,
I can give it a try but if you go to name
and ask it to sort it by population,
it will rise an error, now the error is quite long
but essentially it is saying that there is not
a one-to-one relationship between
the values in the two columns.
What I want to obtain as a result is something different.
I don't want to use the name, I want to create
a new calculated column, which I call
in this example name unique,
and if I add it to my report,
expand everything, now you see that I have
the states which are sorted alphabetically,
whereas the counties are sorted by population.
How did I do that?
Well I need to solve two different issues,
the first one is that I want to create a new column
instead of the population, that has unique values,
and a one-to-one relationship with the name,
and then I need also to create a new name
so that different counties in different states,
with the same name, appear as different...
uh, different values.
The reason is, they will not have the same sorting
in all the states, so they need to be different.
Let's solve the problem one by one.
First let's talk about the name.
Let's see how the name unique column has been created,
let me just increase the font a bit,
okay, the technique that I'm using for this
code has already been explained in another video,
and if you curious about it,
go take a look at the video,
where I give a full explanation of how it works,
and basically enumerate all the places,
which are in the same states or the counties,
which are in the same states,
and then I rank them one by one,
so to assign one number to each different county,
and finally I use the zero-width whitespace
to append at the end of each name a different value.
At the end all the names will be different,
and this solves the problem for the county.
What about the population?
Well population has different values
but I created a sort key column,
how do I create the sort key?
Well I need a different value for
each value of the population but in case
I have two different counties with the same population,
I want to assign them to different sort keys.
There are a lot of ways of building it,
what I did is I first computed the number
of rows in the table and I need it
because I have two columns in my sorting,
one is the population, the other one
is the census key, it's just a number that
enumerates all the different counties.
The population is much more relevant than the key.
So in my sort order I will multiply
the population by the number of rows,
so to obtain a huge number that will guarantee
that the census key will be only a secondary sorting.
So I first retrieve all the key in population
and then I RANK over this table,
the expression, census population times
times number of rows plus the key.
The result is a number that grows from one to
the number of keys in a monotonic way.
Once I have it, now I can go to name unique
and use sort by column and sort it by the key.
Once this is done, I have my report
and I can choose, instead of the name,
the unique name that will create the correct sorting.
Now sorting doesn't look like a very important topic
to cover, indeed it is because if you provide
the correct sorting to your data,
then your users will see first the most
relevant information, and browsing the data
will be much much better.
If you like this kind of topics, please come to
SQLBI.com where you will find a full article
explaining the technique that I described here
and you will also have the option of downloading
the pbix file so you can play with it and learn more.
Have fun with DAX!