Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ThomasBruneel
Frequent Visitor

filtering on matrix with nested rows

Hey guys,

 

I have following table:

ThomasBruneel_0-1649855097642.png

I have a matrix and filter with the following structure:

ThomasBruneel_1-1649855199779.png

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:

ThomasBruneel_2-1649855732153.png

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?

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

See if this works for you.

First the model:

 

model.jpg

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:

filter pane.jpgTo get:

and.gif

 I've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

5 REPLIES 5
PaulDBrown
Community Champion
Community Champion

See if this works for you.

First the model:

 

model.jpg

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:

filter pane.jpgTo get:

and.gif

 I've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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.

 

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.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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:

ThomasBruneel_0-1649936813046.png

Is their a solution for this issue?

amitchandak
Super User
Super User

@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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.