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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
subhendude
Employee
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


View solution in original post

2 REPLIES 2
subhendude
Employee
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


Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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