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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
subhendude
Microsoft Employee
Microsoft Employee

Filtering matrix rows by a column value

I've a dataset which has few test cases running in two locations.

subhendude_0-1685708337318.png

I'm able to pivot it using the matrix table like below.

subhendude_1-1685708456131.png

However I would like to show it using a matrix table with the rows where the test cases didn't run for either of the location like below

subhendude_2-1685708554087.png

Can anyone help how to show this in a matrix table?

 

Thanks!

1 ACCEPTED SOLUTION
Ritaf1983
Super User
Super User

Hi @subhendude 
You can achieve the desired result by adding 3 measures:
Firstly counters for Paris and London test cases :

LondonCount = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Location]="London"))
ParisCount = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Location]="Paris"))
At this stage we will get your "pivoted" table:
Ritaf1983_0-1685796047335.png

The next step is to create a "flag" measure which you can use as a filter:

TestCount = if([LondonCount]=0 || [ParisCount]=0,1,0)
Now you can put it on the visual filter and filter by its values is 1:
Ritaf1983_1-1685796205589.png

If you need red formatting for blank cells, you can base it on the first measures too.

Ritaf1983_2-1685796650865.png

 

Result:

Ritaf1983_3-1685796714933.png

Link to a sample file 

 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly


Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

2 REPLIES 2
subhendude
Microsoft Employee
Microsoft Employee

Thanks!

Ritaf1983
Super User
Super User

Hi @subhendude 
You can achieve the desired result by adding 3 measures:
Firstly counters for Paris and London test cases :

LondonCount = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Location]="London"))
ParisCount = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Location]="Paris"))
At this stage we will get your "pivoted" table:
Ritaf1983_0-1685796047335.png

The next step is to create a "flag" measure which you can use as a filter:

TestCount = if([LondonCount]=0 || [ParisCount]=0,1,0)
Now you can put it on the visual filter and filter by its values is 1:
Ritaf1983_1-1685796205589.png

If you need red formatting for blank cells, you can base it on the first measures too.

Ritaf1983_2-1685796650865.png

 

Result:

Ritaf1983_3-1685796714933.png

Link to a sample file 

 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly


Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.