Forum Discussion
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 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.
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
5 Replies
Anonymous check this:
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".
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.
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
