Apply filters when a chart is filtered

[karinacchan]

Hi everyone!

I have a matrix and a bar chart. By default, I want the data in the matrix to be affect by a date filter while the bar chart is not. But when the user select a value in the matrix, I want the bar chart to be affected by the date filter as well. 

I have inserted two images below for demonstration. When nothing is being selected on matrix, I want the bar chart to show all the Reporting manager (image 1 left bar chart having 4 Managers instead on 3), but when a value is being selected in matrix, I want the bar chart to show only the manager who is responsible for the shift (image 2 right bar chart showing Maggie only).

I have tried to create a date lookup table and used TREATAS in an IF statement hoping I can only apply the date filter when the condition is true but for some reason the virtual relationship is built even when the statement is false (it would be great if any of you could explain this to me because I cannot wrap my head around this) (see image 3). 

I don't have permission to upload workbook so I have put that in my drive. Thank you very much!!
Link to workbook 

Image 1:
Image 2:

Image 3: 

[Anonymous]

Hi @karinacchan ,

I create a measure and here is the DAX code.

Manager Measure = 
IF(
    ISBLANK(SELECTEDVALUE(Roster[Reporting Manager])),
    CALCULATE([Filtering], ALL(Roster[Date])),
    [Filtering]
)

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[karinacchan · Accepted solution]

Hi Yilong Zhou,

Thanks so much for the reply. But I actually only want one manager in the bar chart when a shift is being selected.

I have found a solution yesterday night, after I realised I shouldn't be using SELECTEDVALUE because in cases where the same manager is managing the same shift for other days, it returns null because there are more than one name in the cell. 

I created a disconnected lookup table "Date Lookup" with the following DAX and then used it to filter the bar chart as "Filter Manager = 1" and it works! Thanks for your help anyway:) 

Filter Manager = 
IF(ISFILTERED(Roster[Lane]),
CALCULATE(
        COUNTROWS(VALUES(Roster[Reporting Manager]))
        ,TREATAS('Date lookup',Roster[Date])
    )
,CALCULATE(
        COUNTROWS(VALUES(Roster[Reporting Manager]))
    )
)