cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sime
Frequent Visitor

Matrix Filtering by (Rolled up) Top Level Value

Hi All,

 

I have a dataset which contains project stage values and these roll-up to the project and total in a matrix visual.

 

Project - Stage                                 % Complete

Sime_0-1620742792593.png

There is a % complete column in the dataset and I would like to be able to filter the entire project from the list ONLY if all of the stage level values are 100% complete and therefore the Top-Level % completion value is 100%.

 

Should the Top Level NOT EQUAL 100% then all of the stages should not be filtered regardless if one or more stages are 100% complete.

 

What would be the best way to achieve this please?

Thank you.

 

 

 

1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
Community Support

Hi, @Sime 

According to your description and sample pictures, I can roughly understand your requirement. I‘ve also entered some test data to create a matrix like yours, then I figured out a measure to achieve that output you want to get. You can try my steps:

This is the original matrix I created:

v-robertq-msft_0-1620899073727.png

 

I created a measure like this:

Measure 2=

IF (

    ISINSCOPE ( 'Table'[Level1] ) && NOT ( ISINSCOPE ( 'Table'[Level2] ) ),

    IF ( [Measure] = 1, BLANK (), [Measure] ),

    [Measure]

)

Then go to the matrix to replace the measure in the value with the new measure:

v-robertq-msft_1-1620899073732.png

 

And you can get what you want.

You can download my test pbix file below

 

More info about the ISINSCOPE () function in DAX

 

Thank you very much!

 

Best Regards,

Community Support Team _Robert Qin

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

View solution in original post

6 REPLIES 6
v-robertq-msft
Community Support
Community Support

Hi, @Sime 

According to your description and sample pictures, I can roughly understand your requirement. I‘ve also entered some test data to create a matrix like yours, then I figured out a measure to achieve that output you want to get. You can try my steps:

This is the original matrix I created:

v-robertq-msft_0-1620899073727.png

 

I created a measure like this:

Measure 2=

IF (

    ISINSCOPE ( 'Table'[Level1] ) && NOT ( ISINSCOPE ( 'Table'[Level2] ) ),

    IF ( [Measure] = 1, BLANK (), [Measure] ),

    [Measure]

)

Then go to the matrix to replace the measure in the value with the new measure:

v-robertq-msft_1-1620899073732.png

 

And you can get what you want.

You can download my test pbix file below

 

More info about the ISINSCOPE () function in DAX

 

Thank you very much!

 

Best Regards,

Community Support Team _Robert Qin

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

View solution in original post

Hi Robert,

 

Thank you for your reply and for the attached pbix example.

Whilst this filters the matrix as I have requested in my example, I was wondering if it was possible to achieve the same result by adding a custom column to the table in Power Query to achieve the same result?

 

Is this something that can be done using Power Query?

Hi, @Sime 

As far as I’m concerned, the operations in the Power query are aimed at preprocessing the data imported into the power BI, so then the data has been imported into the power BI, the Power query can’t have any relationship with the data displayed in the visuals. Therefore, I think the only way to achieve this is using the measure to function as the value of the matrix to display different values for the different row levels.

 

One blog about Power BI: M vs. DAX and Measures vs. Calculated Columns

 

Thank you very much!

 

Best Regards,

Community Support Team _Robert Qin

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

lbendlin
Super User
Super User

Please explain what you mean by "filtered". Show the expected outcomes for the two scenarios.

Sime
Frequent Visitor

I'm assuming I could accomplish this by creating some form of an array based on the project number and then loop through each and ALL of the stages to check if the overall average equals 100%.  If so then do "this" ELSE "do this"

 

Just not sure how to get there and the best option to use.  Thinking using M and writing to a custom column would be preferrable in this case?

Sime
Frequent Visitor

Hi,

 

Thank you for your reply.

When I use the term "filtered" I mean removed from the matrix by the selection of a custom filter or similar.

 

In the following example, I have 5 projects with their containing sub stages.

 

All of these projects contain sub stages that are at various stages of completion and these all roll up to the top level weighted average completion figure.

What I am trying to achieve is the ability to filter complete projects out of the matrix visual ONLY when the weighted average completion value is 100%.  This would require that all of the sub stage values equal 100%.

 

Projects that the weighted average is NOT 100% but may contain sub stages the ARE at 100% would still need to be included in the filtered view.

 

Filter NOT Active View

Sime_2-1620795406667.png

 

 

Filter Active View

 

Sime_1-1620795337723.png

 

In the Filter Active View, project 0011324.000 has been removed from the current matrix view as the top level weighted average and ALL sub stage % complete values equaled 100%.

 

Hope this clarifies my request.

 

Thanks.

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!