Power BI : Link Charts on DrillDown

Problem:

In Power BI we can't synchronize multiple visuals when we perform a Drill down / up on one of the visuals. Although this feature has been highly voted by the community here, it is not yet achieved by the Power BI team.

Solution :

I was inspired by a feature presented by Daniil Maslyuk in his blog about dynamic hierarchy management and I applied the principle to date hierarchies which is most often used as an axis for Drill down / up.

The idea is to create a table-like hierarchy calculated in Power BI / Cube SSAS then used in a Slicer. For this demonstration, I used the AdventureWorksDW2017 dataset : 

We start by adding a calculated table:

Custom Date Hierarchy =
UNION (
    SELECTCOLUMNS (
        'DimDate';
        "DateKey"; DimDate[DateKey];
        "Name"; DimDate[CalendarYear];
        "Level"; "Year";
        "LevelNumber"; 1;
        "SortColumn"; DimDate[CalendarYear]
    );
    SELECTCOLUMNS (
        'DimDate';
        "DateKey"; DimDate[DateKey];
        "Name"; DimDate[EnglishMonthName];
        "Level"; "Month";
        "LevelNumber"; 2;
        "SortColumn"; DimDate[MonthNumberOfYear]
    );
    SELECTCOLUMNS (
        'DimDate';
        "DateKey"; DimDate[DateKey];
        "Name"; DimDate[DateKey];
        "Level"; "Date";
        "LevelNumber"; 3;
        "SortColumn"; DimDate[DateKey]
    )
)

The idea is to make UNION ALL on the same table by selecting each time a different level, in this logic I added a column "SortColumn" to be able to use it as sort column for the column "Name".

Once the table is created, we add the link with the date dimension (Remember to check the bidirectional filter):

We go to the Report tab and we start by adding 2 visualizations of type Charts, for the first Viz we configure the attribute Name Hierarchy Custom Hierarchy as Axis, we configure SalesAmount as column values ​​and EnglishProductCategory as column series, on the second Viz we duplicate the first and we just change the series column on EnglishProductSubCategory:

Add a Slicer with the Level attribute of Hierarchy Custom Hierarchy as Field

And finally I used a Custom Viz HierarchySlicer to display the Date hierarchy flat:

Et voilà !!!

Of course we can decline the use of this mechanism with other types of Viz.

Here is the full version of the report here

Enjoy !!

French Version