- Ciao friends.
In this video,
I want to show you how to use calculation groups
to let your users choose between multiple dates
that might be present in your model.
Let's start immediately looking at the example.
I have this report that is showing by year and month
the sales amount and the sales quantity
and the distinct number of products
that were sold in a given period.
Everything is working just fine.
But if we take a look at the data model,
you see that I have multiple dates in this model.
I have the delivery date,
the day when the order is delivered
and the order date, the date when the order is placed.
And because I have two dates,
I have created two relationship between sales and date.
The first one is based on the order date and it is active.
The second one is based on the delivery date,
and that is kept inactive in order
to avoid ambiguity in the model.
I want to be able to build not only this report
but also a report that uses...
the relationship based on the delivery date.
So the numbers are different in this report
because I'm showing the delivery amount,
the delivery quantity and the delivery product.
And I already did it by creating several measures.
I have the delivery amount that computes sales amount
and by using CALCULATE it changes the active relationship
by using USERELATIONSHIP.
In a similar way, I created the delivery products
and the delivery quantity.
The thing is, if I have two relationships
and three measures, I end up building six measures,
and the number of measures increases
with the increase of number of measures and relationship.
That's why I want to do something different.
I want to reuse the existing measure,
sales amount, quantity and products,
but use a slicer to choose between the order date
and I still have the same values
that I have for the sales amount,
but when I click on delivery date,
the same calculation will work
under a different relationship.
I want to do everything using calculation groups.
So what I did is creating a calculation group
that has two values,
one that returns the same value as the sales amount
and a second value, a second calculation item
that will activate the inactive relationship
and then compute sales amount
or whatever measure the user put in the report.
Now, the solution is already there,
but what I want to do is show you step by step,
how to create this solution.
So we are now going back to the Power BI desktop file
remove the current solution and rebuild it together.
Let's start.
First of all, we need to kill the current solution.
So let me go here and delete this calculation group.
Of course my report will stop working,
but that is something we will fix step by step.
What I need to do is go to External Tools,
launch Tabular Editor,
and then I switch to it.
In Tabular Editor I need to create a new calculation group.
So under table, I chose new calculation group
and we need to provide a name, date to use.
We also need to provide a name for the column
of the calculation group, we use again date to use.
And finally, we need to create calculation items.
The first one will not do anything.
So it just return SELECTEDMEASURE.
By returning SELECTEDMEASURE
it does not change the behavior of the current measure.
Once this is done, I forgot to give it a name,
I name it order date because this is
the calculation group that uses order date.
I save it,
if I go back to Power BI Desktop,
you see that it says that one or more calculation groups
need to be refreshed.
Let's do it.
I still have the old selection.
Now I have only one calculation item, order date.
And if I click on it, nothing happens.
That's totally expected
because my calculation item right now
is not changing the behavior.
Let's build the second calculation item
that will activate the desired relationship.
So I create a new calculation item.
This time I call it delivery date,
and in the code I will now use CALCULATE
the SELECTEDMEASURE
and then USERELATIONSHIP because I want to activate
a different relationship.
And I activate the relationship between
sales delivery date
and date date.
That's it, whenever I will call this calculation item
I will use this calculation item,
the SELECTEDMEASURE will be executed
in a new context where the active relationship
is the one that I want.
I need just to confirm it, go back to file,
save,
switch to Power BI Desktop,
and I need to refresh it again
because I created a new calculation item.
And now I have order date and delivery date.
Order date is still computing the same value,
but if I click on delivery date,
now you see that the same measures
that I was using earlier
are now using a different relationship.
As you have seen, you can use calculation items,
which are an extremely powerful feature
also to do simple things like change the active relationship
between the multiple ones
that you typically have with the date.
You might also have different dimensions
that have different relationships,
but usages like these are very simple and very effective.
If you are interested in these topics, go to sqlbi.com.
There you will find the article that describes
this technique, and you will also be able
to download the demo file, start playing with it,
in order to learn how to implement it in your models.
Enjoy DAX.