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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper 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