Secondary suppression in a matrix table

[Axit]

We have to deal a lot with data confidentiality. At the moment I can do primary suppression using this formula in DAX:

Patient Counts =
IF (
CALCULATE ( SUM ( Patient[counter] ) ) < 5 && SUM ( Patient[counter] ) > 0,
"< 5",
CALCULATE ( SUM ( Patient[counter] ) )
)

That works great if there are no totals or sub-totals in the table. However, we do want to include totals for our clients. The problem is if I've suppressed a value in the table of 4, the remaining value is 6 and the total is 10, somebody could quickly work out that the "< 5" value is 4. Therefore, I need to use what we call "secondary suppression" to suppress the 6.

Thank you for your time.

[v-shex-msft · Accepted solution]

Hi @Axit,

You can try to add condition on category column to ignore calculate on total level.

Sample measure:

Patient Counts =
IF (
    HASONEVALUE ( Patient[Category Column] ),
    IF (
        SUM ( Patient[counter] ) < 5
            && SUM ( Patient[counter] ) > 0,
        "< 5",
        SUM ( Patient[counter] )
    )
)

Notice: Replace bold part with your category column name.

Regards,

Xiaoxin Sheng

[Axit]

Thanks @v-shex-msft this works and stops the calculation for total. Is it at all possible to keep totals where there is no suppression in a row?

[v-shex-msft]

Hi @Axit,

So you means you want to prevent subtotal level and keep total level displayed?

Maybe you can try to use below measure:

Patient Counts =
IF (
    HASONEVALUE ( Patient[Category Column] )
        || COUNTROWS ( Patient ) = COUNTROWS ( ALLSELECTED ( Patient ) ),
    IF (
        SUM ( Patient[counter] ) < 5
            && SUM ( Patient[counter] ) > 0,
        "< 5",
        SUM ( Patient[counter] )
    )
)

Regards,

Xiaoxin Sheng