Gantt Chart Visual Matrix - filter columns and rows with one slicer

[mhirczi]

Hello,

I'm new to Power BI and need some help for the problem I'm facing with filtering.

I've created a gantt chart using the visual matrix to show our audit program. The requirement is to filter the rows and columns with one slicer.

The slicer I use is related to the table audits 

   

I've managed to achive the values with following measure:

MeasureGantt(0/1) =

VAR AuditDate_From = SELECTEDVALUE(Audits[From]) <= MAX (DateTable[Date])

VAR AuditDate_To = SELECTEDVALUE(Audits[To]) <= MAX (DateTable[Date])

VAR AuditStatus = CALCULATE(MIN(Audits[State]), REMOVEFILTERS('DateTable'))

VAR AuditDate = CALCULATE(SELECTEDVALUE(Audits[From]) <= MAX (DateTable[Date]) && SELECTEDVALUE(Audits[To]) >= MIN (DateTable[Date]))

VAR StatusResult =

SWITCH(

    TRUE(),

    AuditDate && AuditStatus = "Open", 1,

    AuditDate && AuditStatus = "Closed", 2

)

Return

IF(AuditDate,StatusResult,0)

The result is as expected:

The problem I have is that the visual does not filter the columns based on the filter selection. If I select only year 2022 only the rows are selected but the column for 2023 are still displayed.

My second idea was to change the measure so that no zero values are displayed. I've change the measure to return the variable StatusResult as result.

In this case only columns with values are displayed when I use the filter:

I know that the visual has the possibility to drill down eacht year seperatley. But what we want to achieve is that the user selects the year (or multiple years) in the slicer and no further user action is required. 

The filter result should look like this:

I hope anyone can help with this.