Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Filter matrix based on values columns

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...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
Anonymous
Not applicable
4 years ago

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".