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

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?