- Ciao, friends.
In this video, I want to show you a creative usage
of CONCATENATEX.
And to do that, I want to show you a report.
Let's go straight there.
In this report, I'm showing the year, the month,
the sales amount and the country with most sales,
which is showing the best country that month.
So Country With Most Sales is telling me that in
January 2008, Australia, with 40.23% of share
was the best country for sales.
Sometimes there might be ties.
In February, both United States and United Kingdom,
they both are in the top position, with 28% of sales.
So there might be ties.
I'm actually cheating.
I'm using rounded sales in order to force ties.
But you get the point.
I need to handle both the scenario where I have only
one top seller, or when I have multiple top sellers.
Let's go and look at the DAX code.
So this is Country With Most Sales.
It looks a very complex piece of DAX.
Actually, it's just long, it's not complex.
What we want to do is find the top seller.
And we sort them by their share on sales.
So we start computing the total sales,
the grand total of sales in that month.
Then we create a temporary table, country sales,
which contains for each country, the sales amount.
So Total Sales is the grand total
and Country Sales is the total country by country.
Remember, we are still speaking about
a given month, a cell in the report.
Then we transform the sales into a percentage,
dividing the sales country by country, by the total sales.
So now we have a percentage.
And finally, because what we want to show is the country
with the percentage together as a description,
let's take a look at the result, which is here.
It's United States and then 34.64% that is a string
that we need to construct.
So, what we need to do is build this string
and this is happening in this variable country description,
where... we add a new column to country description,
which is Country Region and then the percentage
formatted as a string.
Now the table is ready.
We just need to extract the first row from this table
and use it to return it as a result.
The thing is, we are never sure
that there will be only one winner.
Because of ties, TOPN, which is the function we are using
here in Top Countries, might return multiple rows.
So Top Countries might contain one, two, three,
any number of rows.
We never know.
So we cannot return straight Top Countries,
or just isolate the column and return it.
It might not work.
That is when CONCATENATEX becomes useful.
CONCATENATEX let me iterate over this table,
and concatenate strings, which are the result
of expression that I specify here.
So, the final result just uses CONCATENATEX
over Top Countries, the last variable.
We take the result, the result is the description
that we want to produce as the result,
and as the separator, we use a comma,
and UNICHAR 10, which is the new line.
So all the ties will be separated by a new line.
We do all this only if there are sales,
in order to avoid showing any row where there are
no sales, because with no sales,
all the countries will be in the top 10, with zero sales.
The final result, as you see, is quite a nice report,
that shows the year, the sales, and the top seller.
Of course, you can use it for whatever kind of
TOPN or whenever you have a table
and you want to return rows from that table
concatenated as a string, no matter how many rows are there.
Have fun with DAX!