Filter matrix if a slicer is not selected

[victoriashenton]

I have a matrix where the Column is 'Status' (e.g. complete, in progress, overdue, on hold) and the values are a count of these status. I also have a slicer which filters on these status. 

Is it possible to filter the matrix to only show 'In progress' and 'Overdue' when the slicer is not selected however if complete or on hold is selected in the slicer then it is shown in the matrix? 

[shafiz_p]

Hi @victoriashenton 

As I can understand that you have Status column and a count value of those status in matrix. See image of the scenario, you have described:

Also, you have a slicer of the same status column, and you want, if you don't select slicer, matrix should show value of only status "In Progress" and "Overdue", other wise show value according to slicer selection i.e complete or on hold or both.

My solution:
I have used a disconnected table for slicer of the same status column. You can use dax or PQ to create such table of distinct status values. No relationship with other tables.

See image for disconnected table(Used this status column in the slicer):

I have developed a measure to count status according to your requirement. This measure will count status for only "In Progress" and "Overdue" if slicer is not selected. Find code below:

Count = 
VAR _isfiltered = ISFILTERED(DistStatus[Status])
VAR _selectedValues = VALUES(DistStatus[Status])

VAR _Result =
IF(
    _isfiltered,
    COUNTROWS(
        FILTER(
            TabOrder,
            TabOrder[Status] IN _selectedValues
        )
    ),
    COUNTROWS(
        FILTER(
            TabOrder,
            TabOrder[Status] IN {"In Progress", "Overdue"}
        )
    )
)

RETURN
_Result

If slicer is selected, it will show only the selection, it could be multiple selection.

Now place this measure in matrix value section, go to filter pane, select that count measure to is not blank. See image below:

Desired output:

Slicer not selected:

After slicer selection, single and multiple:

Hope this helps!!

If this solved your problem, please accept it as a solution!!

Best Regards,
Shahariar Hafiz

[victoriashenton]

Apologies I didn't make it clear in my initial message but I have the different status' as the columns and the rows are actually people's names

So what you have done for the rows is what I would like to do for the columns

[victoriashenton]

@bhanu_gautam Thanks for your assistance. When I do the ShowStatus DAX I am getting the following error:

A single value for column 'FilterStatus' in table 'FilterSort' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

[shafiz_p]

Hello @victoriashenton , You are getting this error of the solution provided by @bhanu_gautam , because you have used naked column in measure, which is not possible without using any aggregation such as min, max. See images:

Hope this helps!!

[bhanu_gautam]

@victoriashenton , First create a measure that checks if 'Complete' or 'On hold' is selected in the slicer.

SelectedStatus =
IF (
ISFILTERED('YourTable'[Status]),
IF (
COUNTROWS(
FILTER (
VALUES('YourTable'[Status]),
'YourTable'[Status] IN {"Complete", "On hold"}
)
) > 0,
1,
0
),
0
)

Create another measure that will be used to filter the matrix based on the slicer selection.

DAX
ShowStatus =
IF (
[SelectedStatus] = 1,
1,
IF (
'YourTable'[Status] IN {"In progress", "Overdue"},
1,
0
)
)

Add your matrix visual to the report.
Drag the ShowStatus measure to the visual-level filters pane of the matrix.
Set the filter to show only items where ShowStatus is 1.