- Ciao, friends.
In this video, I want to show you some practical usages
of calculation groups.
We will use Contoso as an example
and I will use calculation groups
to create dynamic format strings on a regular measure.
And then I will use, again,
a calculation group to perform
the dynamic conversion of the unit of measure
of the weight of products.
Let's start looking at the example first.
At Contoso, they have products.
And they have the product name, the weight
and the unit of measure that is needed
to give a meaning to the weight.
So 1.50 pounds, 29 grams and so on.
Now, whenever you have a number and a unit of measure,
you need to pay attention
when you aggregate values together.
Let's try to compute the total weight that was shipped.
You can author a simple calculation like this one
that computes the weight by iterating over sales
and computing quantity times the weight
of the RELATED product.
The measure works fine and produces a number.
The thing is, this number is wrong.
You can easily see if you add the unit of measure
to the report.
You see that Audio has grams, ounces and pounds.
These three values cannot be aggregated together.
This number for Computers is expressed in pounds
and that is correct,
but you cannot sum these three values together,
which is exactly what is happening
if you do not add the weight unit of measure
to the report.
So what I want to do,
I want it on the rows,
is something different.
I want, first, to fix up this measure
and get rid of the total
if the total cannot be computed.
What I want to build is a calculation like this
that does not show the total
in case it is aggregating different unit of measures.
And I also want to go a step farther.
In case I can show a number,
I also want to show the unit of measure
as part of the result.
Now, the problem of showing the unit of measure
as part of the result
is that the unit of measure depends on the product.
If there is only one unit of measure visible,
I want to add that unit of measure to the format string.
And regular measures do not have dynamic format strings,
but calculation groups do.
So what I will do is create a calculation group
that I called here WeightFormatString.
If I use no conversion,
it will not do any kind of conversion,
but if I use apply format string,
then it will show the measure
using the dynamic format string.
Let's see how I did it.
We need to go to External Tools, launch Tabular Editor.
Okay.
Go to table and here is
the calculation group that I created.
Now, no conversion simply does not do anything.
It returns the SELECTEDMEASURE.
Just need some space because the next measure
would be a bit larger.
Good.
So, no conversion does not do any kind of conversion.
Apply format string on the other hand,
does some operations.
This is the code of the calculation item.
It checks, first, that the selected measure is the weight,
we do not want this calculation item
to apply to other measures,
and then if there is only one value for the unit of measure,
then it shows the SELECTEDMEASURE.
Otherwise, it doesn't show anything.
If any other measure is the SELECTEDMEASURE,
then it returns whatever value
was put by the user in the report.
And this is useful to compute the number.
Let's take a look at the format string.
It's a dynamic format string so it is a DAX expression.
First, it checks if the SELECTEDMEASURE is the weight
as we did before, then it retrieves
the current format string
by using SELECTEDMEASUREFORMATSTRING
and using a default value in case the measure
does not have a format string.
Then it computes the current unit of measure,
this expression returns a unit of measure
only if there's only one selected.
And finally, the result, IF there is a unit of measure,
it takes the current format string
and the current unit of measure.
As you see, the result is that the measure
does not show anything
in case there are multiple unit of measure,
but it shows a value here for Home Appliances
and here for Music, Movies
because all the values are expressed in pounds.
Beside, the format string is now dynamic
and it changes for every row.
The thing is we can go a bit farther.
What we can do is provide a total
even when there are multiple format strings,
multiple units of measure.
To do that, we need to define the target unit of measure.
We will let the user choose the target unit of measure
by enriching the calculation group.
What I want to do is this last example
where I have, again, the same calculation group,
but I added multiple values, grams, ounces and pounds.
This is the behavior we have seen so far,
but if my user selects grams,
then the entire calculation happens in grams
by doing the automatic conversion.
And of course, a user has the option of changing
the unit of measure at will.
Let's take a look at how I implemented that.
Let's take a look at ounces, for example.
This is the code of the expression that, again,
checks that the selected measure is weight
otherwise it will not do anything.
If I need to do the conversion,
I need to have a table that gives me the factor
to use in order to multiply the weight expressed
in the unit of measure of the product
by the unit of measure that the user want to use.
So this table contains two column,
the source unit of measure and the multiplication factor.
We know that the target will be ounces.
So ounces to ounces has a factor of one,
pounds to ounces has a factor of 16,
grams to ounces has a different factor.
Then I need another table
which I create on the fly
that contains the weight expressed
in the original unit of measure
for the current selection.
So I take...
the current values of the unit of measure,
there might be multiple, and I compute, first,
the same unit of measure and then the total weight.
I compute the total weight using SELECTEDMEASURE
because in this way my calculation item
can be applied also to other measure in case I need it.
Some special words are needed for this expression,
because ADDCOLUMNS creates a table
that contains the weight unit of measure and the weight.
So I already have the unit of measure,
why do I create another column with the same content?
If you look here, I have, in unit of measure,
the weight unit of measure,
which is the column in the model,
and I add just an empty string.
So the result is the same,
but I use this technique to lose the data lineage,
because I will want to join the result of weight
with this conversion table.
And that can be done only if both columns
do not have data lineage
or if they have the same, but in this case,
I need them to have no data lineage.
Finally, I use NATURALLEFTOUTERJOIN.
This is not a very commonly used function,
but it is extremely useful because it makes
the join between two tables.
So I'm joining weights
with the table containing the conversion.
And the join will happen based on the unit measure column
that appears in both tables.
The resulting table contains the unit of measure,
the weight and the conversion factor.
So the last step is just iterating over this table,
do the final multiplication and produce the result.
Let's take a look at the format string expression
that in this case is very simple.
Because we already know
that we are doing a conversion to ounces,
so we just take the SELECTEDMEASUREFORMATSTRING
and we convert it to ounces.
As you see, the result is simple
because I have the option of moving from grams
to ounces to pounds in a very simple way.
You have seen that calculation groups
are extremely powerful.
We have used for two purposes.
First, we use them to force dynamic format strings
in regular measures.
And then the second step,
we use them to perform on-the-fly conversion
of unit of measures.
I know, looking at the video, everything seems easy.
That's why I suggest you to go to sqlbi.com,
read the full article, download the file
so that you can start playing with it and learn by doing.
That is the best way to start learning calculation group
and use them in your models.
Enjoy DAX.
(upbeat music)