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.

Sime's avatar
Sime
Helper I
5 years ago

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?

v-robertq-msft's avatar
v-robertq-msft
Community Support
5 years ago

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.