Forum Discussion

ThomasBruneel's avatar
ThomasBruneel
Frequent Visitor
4 years ago
Solved

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...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    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's avatar
PaulDBrown
Community Champion
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

ThomasBruneel's avatar
ThomasBruneel
Frequent Visitor
4 years ago

Thank you Paul. This works for me.

One small remark, can I get rid of the Dproject table since it doesn't exist in the filter measure declaration.

 

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    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's avatar
      ThomasBruneel
      Frequent Visitor
      4 years ago

      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?