Filter matrix based on values columns

[Anonymous]

Is there a way to only return rows in a matrix visualization that match ALL criteria provided by a hierarchical slicer? I've Googled this a bit and haven't found a solid solution yet.

Source data structure:

rec_id | attribute_name | attribute value

Slicer (>200 attributes):

>attribute 1

>>attribute 1 value

>attribute 2

>>attribute 2 value

When I select attribute 1 value and attribute 2 value in the slicer, I see all rec_id records in the matrix that contain attribute 1 value OR attribute 2 value. I am trying to find a solution where the matrix only shows records that contain attribute 1 value AND attribute 2 value.

The solution here is very similar to what I'm trying to accomplish, but the slicer values are a flat list and not hierarchical.

[Anonymous · Accepted solution]

Posting my own solution for visibility. I ended up creating a single measure that returns 1 if the slicer selection conditions are met and 0 if they are not. That measure gets applied as a visual level filter to the matrix, which is set to only show items when that value is 1.

#RecordMeetsConditions = 
VAR NumOfSelectedAttrVal = DISTINCTCOUNT ( 'my table'[attr_value] ) 
VAR SelectedNumCols = 
    CALCULATE(
        DISTINCTCOUNT ( 'my table'[attr_name] ) ,
        ALLSELECTED(my table)
    )
VAR bool = IF(NumOfSelectedAttrVal = SelectedNumCols, 1, 0)
RETURN
    bool

[Anonymous]

Hi @Anonymous ,

Please refer to my pbix file to see if it helps you.

Create two table based on the table.

Table 2 = SUMMARIZE('Table','Table'[attribute_name])

Table 3 = SUMMARIZE('Table','Table'[-attribute_value])

Then create two measures.

Measure = CALCULATE(SUM('Table'[-attribute_value]),FILTER(('Table'),'Table'[attribute_name]in VALUES('Table 2'[attribute_name])))

Measure——2= CALCULATE(MAX('Table'[-attribute_value]),FILTER(('Table'),'Table'[-attribute_value]in VALUES('Table 3'[-attribute_value])))

If I have misunderstood your meaning, please provide more details with your desired output and your pbix file without privacy information.

Best Regards

Community Support Team _ Polly

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

[Anonymous]

For my use case, I need the attribute names and attribute values to be in the same slicer with parent-child relationships. You can take a look at the solution I posted for what ended up working for me.

[SpartaBI]

@Anonymous check this:

https://youtu.be/tONOosN1xAs

[Anonymous]

In that video it looks like they are filtering based on a category, not used in the visual. I think I could have been more clear in my post, but I'm trying to filter the columns created by the field in the "Columns" value for the matrix. 

So my matrix setup is like:

Rows

-rec_id

Columns

-attribute_name

Values

-attribute_value

Then I have a hierarchical slicer like:

>attribute_name

>>attribute_value

Since I have over 200 attributes, I would like to use the slicer to produce AND conditions instead of OR conditions. So when selected, I would have criteria like "Attribute 1 Name: Attribute 1 Value" AND "Attribute 2 Name: Attribute 2 Value".