Using Calculation Groups in #PowerBI to implement a Many 2 Many (M2M) filter

Yesterday I tweeted about how I solved the implementation of a Many 2 Many filter using Calculation groups.

And was encouraged to write a blog post about this – so here it is 😊.

The scenario

Imagine a simple star schema where we have a fact table with sales and a customer and Date dimension.

And a in the demo I have also created a few measures – (in my actual customer case I had over 100 measures)

Now we want to expand the model to handle that a customer can belong to one or more customer groups –

This is done by adding the table Customer Groups that contains the Customer Groups and then a bridge table that contains the link between the customers to the different customers groups.

For instance, Customer Key 12031 both belongs to Customer Group 1 and 2.

But when we try to report on the Sales Value the value for the groups will be identical

This is because row context comes from the table Customer Groups and because the filter from that table can’t be passed to the customer table as the cross filter direction points the other way (red circle)

We could change the cross filter direction to Both via the Edit relationship dialog

And it would solve the problem.

But as Alberto Ferrari highlights in this blog post – Bidirectional relationships and ambiguity in DAX – SQLBI – this can be dangerous.

Change all the measures

For all our measures to work I would have to update them all and add a CROSSFILTER statement each of the measure statement

This will give me the result I want but also some work to update all measures.

Use Calculation groups to add filter to all your measures

Instead let’s create a Calculation Group to apply the filter – and the tool to do this is the Tabular Editor – Tabular Editor

We will add a Calculation Group via Tables

And add a group called “Customer Group Filter”

Next step is to add a Calculation Item

I will call it “Apply Customer Group Filter”

And then in the expression editor I will add the expression that applies the CROSSFILTER to the SELECTEDMEASURE().

Clicking Save and returning to Power BI Desktop will prompt me to refresh the calculation groups

Now I will have a Calculation Group Filter in my Fields list

And we can add a slicer or a filter to the visual, page or all pages

And if we select the Calculation Item we can see the filter is being applied.

You can download the demo file – here

Summary

Typically, the calculation groups examples are normally focused on Time intelligence but absolutely not limited to time – I hope this example can give you some inspiration to use Calculation Groups in other scenarios as well.

In my actual case I saved time on updating over 100 measures – so I really ❤ calculation groups.

Let me know what you think and if you find it useful as well.

DAX Time intelligence and the 29th of February – #PowerBI

Yesterday I visited a client and was asked – how do the time intelligence functions handle the fact that February has 29 days in 2020.

Well – in fact there was a few surprises depending on what you select from you date table.

Let’s look as some examples – I will use the following Internet Sales Amount from the years 2011-2013 from Adventure Work Database where we in February 2012 have 29 days.

As you can see, we have the year 2012 where we have 29 days.

SAMEPERIODLASTYEAR()

In order to calculate Internet Sales Amount LY – I use the following

Internet Sales Amount LY = CALCULATE([Internet Sales Amount];SAMEPERIODLASTYEAR(DimDate[Date]))

Which works fine

But notice the behavior if we put dates or days numbers on the rows

SURPRISE – Internet Sales Amount LY will show the value for the 28^th of February 2011 instead of a blank value as you perhaps would expect

If you select year 2013 we will see this

The 29 of feb 2012 will “disappear” but the total for February will include the number.

DATEADD() – last year

If we use the function DATEADD instead – it will work exactly the same way.

IAS LY = CALCULATE([Internet Sales Amount];DATEADD(DimDate[Date];-1;YEAR))

DATEADD() – same day last year

If you want to compare the same Saturday (the 29^th of feb 2020 is a Saturday) last year – which is the 2nd of march we can do this by using the same DATEADD function but with different parameters

IAS LY same weekday = CALCULATE([Internet Sales Amount];DATEADD(DimDate[Date];-364;DAY))

This will compare the same day 52 weeks ago (52 * 7 = 364) and there by giving us the value from the 29^th of feb 2012 on the 27^th of feb 2013.

DATESMTD()

Now what about the function DATESMTD()

ISA MTD = CALCULATE([Internet Sales Amount];DATESMTD(DimDate[Date]))

ISA MTD LY = CALCULATE([Internet Sales Amount LY];DATESMTD(DimDate[Date]))

These functions will calculate the running total for the month for the given day number

Notice that the ISA MTD works fine in 2012 for the 29^th and the LY measure will show the same result for the 28^th and 29^th in 2012 – and in 2013 it will actually for the 28^th show the sum of both the 28 and 29^th 

Conclusion

You might find that some users find it difficult to understand how the calculations works when the look at dates instead of month totals especially in the case where they will get the same value for LY on both the 28 and 29^th in 2012/2020.

If you compare cross years on calendar dates I find the result that SAMEPERIODLASTYEAR() returns makes better sense than leaving it empty/blank but what do you or your users think. Let me know in the comments.

Hope you find this little walkthrough useful.

And remember to ALWAYS use a datetable in your model if you do time intelligence calculations in DAX.