Filter interaction matrix charts

[MagikJukas]

Hello,

I have a matrix table with a hierarchy on the row, years on the column, and values.

On the visual filter, I selected only 2020 and 2021 years.

On the same page, I have a charts with all years and values.

Problem: when I select one element of the hierarchy, the chart is hiding the years that are not present in the matrix table.

How can I select an element on the matrix table and show all the years in the charts?

Thanks

[Whitewater100]

Good morning:

Please see file for potential solution. You will notice data model has Category as dimension table. The Matrix filters so all years with data show, when selected. My first file could have confused as I had another solution intermingled. Now it is on just your issue.

https://drive.google.com/file/d/1QN-ygAv-maX3Eph98jdO2VJneitux7vD/view?usp=sharing 

[v-cazheng-msft]

Hi @MagikJukas,

Actually, OEMToms has provided the solution for your needs. The reason that you see values of 2019 and 2020 years are blank should be something related to your data itself or the relationships in your model or how you calculated these values.

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

[BITomS]

Hi @MagikJukas,

I think you need to use the 'Edit Interations' functionality. Firstly, select your matrix table so that the format option appears in the ribbon. Then select, 'Edit Interactions':

On the chart, you should then see the following icons: 

If you select the circle with the line through it, it will disable the interaction of the table matrix with your chart which I believe is the behaviour you desire.

[MagikJukas]

thanks for your comment @BITomS 

Unfortunately, this is does not work. 

I do not want to disable it, I want to filter in the chart by using the matrix categories. But everytime I click, past years disappear ( because the matrix is showing only the last two years).

[BITomS]

@MagikJukas, depending on the type of chart, the 'Highlight' filtering behaviour can be used (the middle of the 3 options):

This should highlight (filter) the year selected from your matrix table, but also keep any other years visible on the chart, even if they are not in your matrix.

[MagikJukas]

See below: I selected the category Spare parts.

In the chart it only shows 2019 and 2020. I have data for before and after.

If I deselect the category, 2018 to 2022 are displayed.

[BITomS]

You still have the 'Filter' behaviour selected in your screenshot which will not work for what you are trying to achieve. Please change the interaction so that the middle option is selected (Highlight):

Then try your matrix selection again and let us know how this goes.

[MagikJukas]

as you can see, it evaluates there is nothing outside these periods.

I suspect the solution is to build a formula that ignores the period... I am struggling to figure it out though.

[BITomS]

Apologies, I must have misunderstood your original post, because your last screenshot looks like what I thought you were requesting (selecting a single category, but the chart still showing all data for all years). I've now caught onto the fact you only want 'Spare Parts' for all years in the chart.

In terms of a formula, it looks like @Whitewater100 has suggested something, but I would question why you need to introduce complex formula logic (However, I don't understand your use case). For instance, you could add a slicer on the canvas for 'category', which updates both the matrix and chart to achieve what you need. Or you could change the visual vilter on the matrix from using the date, to where values are 'not blank', so no years are obfuscated, and then user selection on the matrix will not limit the chart.

Like I say, I don't understand your use case, so the above suggestions may not work, but thought it may be worth calling out the alternative design options to effectively achieve the result.

[Whitewater100]

Hi:

I can be way off. Do you have separate Date Table which is being used for any date fields in your visuals? 

I'll attach a basic file with date table and paste date table code bellow if you need it. The date table must be marked as date table and have relationship with your fact table. This file example will demonstrate this.

https://drive.google.com/file/d/14G9U23xIV9_f57l2M7ChY-KWPif8M3Dp/view?usp=sharing 

Date Table MODELING>NEW TABLE > Paste this in..

Dates =

-- Specify a start date and end date

VAR StartDate = Date(2021,1,1)

VAR EndDate = Today() + 243

VAR FiscalMonthEnd = 12

-- Generate a base table of dates

VAR BaseTable = Calendar(StartDate, EndDate)

-- Add the Year for each individual date

VAR Years = ADDCOLUMNS(BaseTable,"Year",YEAR([Date]))

-- Add the calendar month and other month related data for each date

VAR Months = ADDCOLUMNS(

    Years,

    "Month",MONTH([Date]),

    "Year and Month Number",FORMAT([Date],"YYYY-MM"),

    "Year and Month Name",FORMAT([Date],"YYYY-MMM"),

    "Fiscal Year", IF( FiscalMonthEnd = 12, YEAR([Date]), IF( MONTH([DATE]) <= FiscalMonthEnd, YEAR([DATE])-1, YEAR([Date]))),

    "Fiscal Month", IF( FiscalMonthEnd = 12, MONTH([Date]),

        IF( MONTH([Date]) <= FiscalMonthEnd, FiscalMonthEnd + MONTH([Date]), MONTH([Date]) - FiscalMonthEnd))

)

-- Add the Quarter and other quarter related data for each date   

VAR Quarters = ADDCOLUMNS(

    Months,

    "Quarter",ROUNDUP(MONTH([Date])/3,0),

    "Year and Quarter",[Year] & "-Q" & ROUNDUP(MONTH([Date])/3,0))

-- Add the Day and other day related data for each date   

VAR Days = ADDCOLUMNS(

    Quarters,

    "Day",DAY([Date]),

    "Day Name",FORMAT([Date],"DDDD"),

    "Day Of Week",WEEKDAY([Date]),

    "Day Of Year", DATEDIFF (DATE(YEAR([Date]),1,1), [Date], DAY) + 1)

-- Add the Week (assuming each week starts on a Sunday) and other week related data for each date   

VAR Weeks = ADDCOLUMNS(

    Days,

    "Week Of Month (Sunday)",INT((DAY([Date])-1)/7)+1,

    "Week of Year (Sunday)",WEEKNUM([Date],1),

    "Year and Week (Sunday)",[Year] & "-W" & WEEKNUM([Date],1))

-- Add an 'Is Working Day' column which will be true for all days but Saturday and Sunday.

var WorkingDays = ADDCOLUMNS(

    Weeks,

    "Is Working Day", NOT WEEKDAY( [Date] ) IN {1,7})

RETURN WorkingDays