Dynamicaly change chart from category to subcategory

Question

I have a hierarchical matrix with a corresponding chart from this table:

let
    t0 = Table.FromRows(
        {
            {"2020-01-01", "1", "10", 10},
            {"2020-01-02", "1", "10", 3},
            {"2020-01-01", "1", "11", 8},
            {"2020-01-02", "1", "11", 15},
            {"2020-01-01", "2", "20", 5},
            {"2020-01-02", "2", "20", 9},
            {"2020-01-01", "2", "21", 13},
            {"2020-01-02", "2", "21", 12}
        },
        {"day", "cat", "subcat", "amount"}
    ),
    t1 = Table.TransformColumnTypes(t0, {{"amount", Int64.Type}})
in
    t1

I can make the page start the chart showing the line for each category, which is what I want, by choosing the category as the legend

Now I want that when I click on the category line in the matrix the chart dynamicaly shows the lines for the subcategories of that category. Is it possible or is there another approach leading to the same result?

Answer 1

I'm not sure if it's possible to do drill down within the visual like that but you can make it work if you have filtering from a slicer or another visual.

First, create a new independent table to use on the x-axis that has both cat and subcat:

CatSubcat = UNION ( VALUES ( t1[cat] ), VALUES ( t1[subcat] ) )

Then we need a corresponding measure to go with it that switches between cat and subcat:

Measure = 
IF (
    HASONEVALUE ( t1[cat] ),
    CALCULATE ( SUM ( t1[amount] ), t1[subcat] IN VALUES ( CatSubcat[cat] ) ),
    CALCULATE ( SUM ( t1[amount] ), t1[cat]    IN VALUES ( CatSubcat[cat] ) )
)

If nothing is filtered, it should look like this:

If you filter using the matrix to the left (or via a slicer on t1[cat]), you get this:

---

For more than two levels, this related post may be of use.