Forum Discussion

Sime's avatar
Sime
Helper I
5 years ago
Solved

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 T...
General Comment
Need Help
  • v-robertq-msft's avatar
    v-robertq-msft
    5 years ago

    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:

     

    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:

     

    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.

lbendlin's avatar
lbendlin
Super User
5 years ago

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

  • Sime's avatar
    Sime
    Helper I
    5 years ago

    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

     

     

    Filter Active View

     

     

    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.

     

  • Sime's avatar
    Sime
    Helper I
    5 years ago

    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?