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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.