Forum Discussion
ThomasBruneel
4 years agoFrequent Visitor
filtering on matrix with nested rows
Hey guys, I have following table: I have a matrix and filter with the following structure: Now I want to get the following when filtering on category "0" and "2", namely only the re...
- 4 years ago
See if this works for you.
First the model:
Create a measure to use as a filter in the filter pane for the matrix as follows:
FILTER = VAR DcatRows = COUNTROWS ( ALLSELECTED ( DCategory ) ) VAR NotFiltered = DISTINCTCOUNT ( 'Table'[project] ) VAR _Rows = CALCULATE ( COUNTROWS ( DCategory ), ALL ( 'Table' ) ) RETURN IF ( DcatRows = CALCULATE ( COUNT ( 'Table'[project] ), ALLSELECTED ( DCategory[dcategory] ) ), 1, IF ( _Rows <> DcatRows, BLANK (), NotFiltered ) )Create the matrix with the rows from the dimension tables, a simple SUM for the values, and add the [FILTER] measure to the filters in the filter pane and set the value to 1:
To get:
I've attached the sample PBIX file
PaulDBrown
4 years agoCommunity Champion
You can in principle, though setting up the model with dimension tables (such as Dproject) is recommended and considered a best practice since it makes the model more efficient.
ThomasBruneel
4 years agoFrequent Visitor
Hey Paul,
I have only one problem when a project has multiple records with the same category the filter doens't work. This is demonstrated in the screenshot beneath:
Is their a solution for this issue?