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 records for project "b" and "c" should be visisble.

When I check those filters now I get the following:

But I don't want to see the records/tab for project "a" because there is no record with project "a" and category "2"

Can someone help?

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

5 Replies

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

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    ThomasBruneel , Try a measure like

     

    measure =
    var _cnt = countx(allselected(Category), Category[Category])
    return
    Sumx(filter(Addcolumns(summarize(Table, [project], [Category]), "_1", [result], "_2", countX(filter(allselected(Table), [project] =max([project])), [Category])), [_2] =_cnt),[_1])

     

     

    You might have use distinctcount