DAX measure applies to only certain rows on a matrix

[JohnW]

Hi everyone,

I am trying to get the values of certian rows in a matrix to only show with selected names.  Is there a way with a DAX measure to only select the rows with a specific name so the DAX filter isn't applied to the entire matrix?  The highlighted rows are the rows that need to have the DAX measure applied to them, the other rows do not need the DAX measure.  Thanks for the help!

[v-eqin-msft · Accepted solution]

Hi @JohnW ,

Please try this:

Measure 2 =
IF (
    ISINSCOPE ( 'Table'[Month] ),
    SUM ( 'Table'[Value] ),
    IF (
        MAX ( 'Table'[Cate] ) <> "Level1",
        BLANK (),
        IF ( HASONEVALUE ( 'Table'[Sub-Cate] ), SUM ( 'Table'[Value] ) )
    )
)

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

[JohnW]

Hi @v-eqin-msft that worked perfectly thank you!  Would it be possible to keep the total for level 1? Also would it be possible for this DAX measure to work without the use of columns in the matrix? Thanks!

[v-eqin-msft]

Hi @JohnW ,

According to my understanding, you want to remove the sub-total of each category in Matrix .

You could use the following formula :

Measure = IF(HASONEVALUE('Table'[Sub-Cate]),SUM('Table'[Value]),BLANK())

The final output is shown below:

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

[JohnW]

Hi @v-eqin-msft thanks for your response.  I am trying to basically remove the subtotals and totals from the rows that are not highlighted.  So in your example I would be trying to only show the total and subtotals in level 1 and no subtotals and totals in level 2 should be shown.  Thanks!